Repeated inserts with Primary key, foreign key

问题:

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.

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.

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..

     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

回答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:

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)