使用多个选择插入表格

I am working on creating stores procedure to insert values in table using select statement. Following is my queuedemail table where i want to insert rows from two diffrent tables.

我正在创建存储过程,以使用select语句在表中插入值。以下是我的queuedemail表,我想从两个不同的表中插入行。

Id          int Unchecked
Priority    int Unchecked
[From]      nvarchar(500)   
FromName    nvarchar(500)   
[To]        nvarchar(500)   
ToName      nvarchar(500)   
CC          nvarchar(500)   
Bcc         nvarchar(500)   
Subject     nvarchar(1000)  
Body        nvarchar(MAX)

First table is campaign which is to be sent to customer

第一个表是要发送给客户的活动

Id             int  
Name           nvarchar(MAX)    
Subject        nvarchar(MAX)    
Body           nvarchar(MAX)    
Sent           bit

I want to take body and subject from this campaign to insert in above table. and there are some tokens in the body which i want to replace with customer name. Now customer values are going to come from customer tables which is having diffrent fields like name, email. Now to and toname in first table are going to come from this customer table.

我想从这个活动中取出身体和主题插入上表。并且我想用客户名称替换身体中的一些代币。现在,客户价值将来自客户表,这些表具有不同的字段,如姓名,电子邮件。现在,第一张表中的toname将来自此客户表。

Any suggestion.

Pardon if not understood. Leave comment if any!!!

请原谅如果不理解。如果有的话留下评论!

Suppose I have customer table with 2 entries like

假设我有2个条目的客户表

Id  F_Name    L_Name    Email 
1   Nitin     Varpe     fedexnit@gmail.com
2   Prashant  Bankar    pbankar@yahoo.com

Now i have campign having body like

现在我有一个像身体一样的露营

Dear %Customer.Name%

//here goes body for the campaign

//这里是竞选活动的主体

Now when I click send campaign. This token quoted in '%' going to be replaced with customer name.and then this body should be inserted in queuedemail table 'to' column by email of customer and toname same as customer name.

现在,当我点击发送广告系列。在'%'中引用的此令牌将替换为客户名称。然后,此主体应通过客户的电子邮件插入queuedemail表'to'列,并与客户名称相同。

Thanx all for ur response.

Thanx所有你的回应。

Hoping to see more from u!!!

希望能从你那里看到更多!!!

3 个解决方案

#1

Try something like this

尝试这样的事情

INSERT INTO queuedemail 
(
    FromName,
    To,
    Body
)
SELECT 
    F_Name,
    Email,
    'Dear' + F_Name +
    (
     SELECT body 
     FROM campaign 
     WHERE -- criteria for selecting body
    )

FROM customer 
WHERE -- your where condition criteria

Include the fields as your needs in insert and select clause

在insert和select子句中包含您需要的字段

#2

where is customer name (which table) ?

客户名称在哪里(哪个表)?

What is relation between token and customer name.How you decide which token to be replace by which customer name .

令牌和客户名称之间的关系是什么。如何确定要用哪个客户名称替换哪个令牌。

first build your require select statement and verify each column and output .Decide about outer join/left/inner.

首先构建你的require select语句并验证每一列和输出。关于外连接/左/内的决定。

will %Customer.Name% several times in body part or only once. will it be same for every row ? If %Customer.Name% will be in every row and each body then this is quite easy you can simply replace with Firstname+lastname.

将%Customer.Name%在正文部分中多次或仅一次。每排都一样吗?如果%Customer.Name%将在每一行和每个正文中,那么这很容易,您可以简单地替换为Firstname + lastname。

#3

Finally came up with solution. Thanx to all for ur support

终于想出了解决方案。谢谢所有人的支持

Below is my stored procedure

下面是我的存储过程

Created temp table to get all the record from customer table.

创建临时表以从客户表中获取所有记录。

GO
/****** Object:  StoredProcedure [dbo].[SendCampaign]    Script Date: 11/19/2013 14:40:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SendCampaign]
  (@CampaignSubject nvarchar(max),
  @CampaingBody nvarchar(max),
    @TotalRecords int OutPut)

AS
BEGIN
   SET NOCOUNT ON

   Declare @Body  nvarchar(max)=@CampaingBody
   Declare @Subject  nvarchar(max)=@CampaignSubject

   Select *
Into   #Temp
From   Customer where Active=1 

Select *from #Temp
   Declare @Id int


     Begin
Insert Into QueuedEmail([Priority],[From],FromName,[To],[ToName],CC,Bcc,
[Subject],[Body],CreatedonUtc,SentTries,SentonUtc,EmailAccountid,Attachment)

  Select  3,'test@test.com','My Site',Email,'Null','Null','Null',@Subject,
dbo.ModifyCampaign(@Body,CAST(NewsLetterSubscriptionGuid as NVARCHAR(200)),Email)
,GEtUtcDate(),0,null,4,'Null'
              from #Temp






     END
    Drop table #Temp

END

Above in select statement ModifyCampaign is the function i have written to replace tokens in campaign with corresponding customer details.

选择语句中的上方ModifyCampaign是我编写的用于替换活动中的令牌以及相应客户详细信息的功能。

Thanx all :) and Hoping that it will help others in future

Thanx all :)并希望它将来会帮助别人

#1