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')
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