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

Answers


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

Need Your Help

Is there any reason to use JavaScript library plugins in grails that expose the given library via TagLibs instead of just using it directly?

javascript grails taglib

If you search the grails plugin site, you'll find lots of plugins for JQuery and other JavaScript libraries. Most of these expose the given JavaScript library functionality through Grails TagLibs....

Android fetch multiple items from sqlite database

java android sql sqlite data-structures

I was like to update (retrieve and possibly edit) multiple items in an SQLite database without using a for/while/dowhile loop

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.