重复插入主键，外键

Can anyone tell me how to do repeated multiple inserts on two tables with primary key, foreign key Here's what I've done. This is a very snippet of what needs to be done. StatusTable has around 200 rows. I am trying to split the details of this Status table into 2- Table1, Table2.

有人能告诉我如何用主键，外键在两个表上重复多次插入吗?这是需要做的事情的一小部分。雕像大约有200行。我正在尝试将这个状态表的细节拆分为2- Table1, Table2。

After inserting each record into Table1, I am getting the Identity column and this needs to be inserted into Table2 with some additional stuff. So if there are 200 rows in StatusTable there are 200 in Table1, Table2.

在将每个记录插入到Table1之后，我将获得Identity列，这需要用一些附加的东西插入到Table2中。所以如果有200行在镇定的表1和表2中有200行。

But thats not the way it is working. It is inserting all the 200 rows into Table1, then getting the Identity and then inserting a single row into Table2. I know why it is doing this. But not sure how to fix it..

但事实并非如此。它将所有200行插入到表1中，然后获取标识，然后将一行插入到表2中。我知道为什么会这样。但不知道如何修复它。

     INSERT INTO [dbo].[Table1]
               ([UserID],  
               ,[FirstName].......)
     SELECT 'User1' AS [UserID]
               ,'FirstName'
     FROM [dbo].[StatusTable]

     SELECT @id =  SCOPE_IDENTITY()

     INSERT INTO [dbo].[Table2]
                ([AccountID],[Status]
           values (@id, 'S')

Please suggest

请建议

2 个解决方案

#1

Use the OUTPUT clause

使用输出子句

 DECLARE @IDS TABLE (id INT) 

 INSERT INTO [dbo].[Table1]
               ([UserID]  
               ,[FirstName])
     OUTPUT inserted.id INTO @IDS          
     SELECT 'User1' AS [UserID]
               ,'FirstName'
     FROM [dbo].[StatusTable]

     INSERT INTO [dbo].[Table2]
                ([AccountID],[Status])
         SELECT Id, 'S' FROM @IDS

#2

Try a set based approach instead of this single row at a time logic. Load the first table, and then you can reference the first table and the data table in the insert to the second table, if you have something that makes each row unique.

在时间逻辑上尝试一种基于集合的方法而不是这一行。加载第一个表，然后您可以引用第一个表和插入到第二个表的数据表，如果您有一些东西使每一行都是惟一的。

you can use a select statement instead of a value list:

您可以使用select语句代替value列表:

insert into table
select rows from othertable (or multiple tables...it's a select statement as complicated as you wish)

Pseudo coded:

伪代码:

Insert into table2 (datacolumns)
select table1.id, datacolumn 
from statustable  s 
inner join table1 t
on (whatever makes these rows unique)

#1