2

I'm working on an application that imports data from Access to SQL Server 2008. Currently, I'm using a stored procedure to import the data individually by record. I can't go with a bulk insert or anything like that because the data is inserted into two related tables...I have a bunch of fields that go into the Account table (first name, last name, etc.) and three fields that will each have a record in an Insurance table, linked back to the Account table by the auto-incrementing AccountID that's selected with SCOPE_IDENTITY in the stored procedure.

Performance isn't very good due to the number of round trips to the database from the application. For this and some other reasons I'm planning to instead use a staging table and import the data from there. Reading up on my options for approaching this, a cursor that executes the same insert stored procedure on the data in the staging table would make sense. However it appears that cursors are evil incarnate and should be avoided.

Is there any way to insert data into one table, retrieve the auto-generated IDs, then insert data for the same records into another table using the corresponding ID, in a set-based operation? Or is a cursor my only option here?

John Straka
  • 1,874
  • 5
  • 28
  • 49
  • 1
    In SQL Server 2008 you can use the `merge...output`. Have a look at this question. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id – Mikael Eriksson Oct 20 '11 at 15:20
  • 2
    BUlk insert to a staging table. Then use MERGE and OUTPUT as others have descibed. – HLGEM Oct 21 '11 at 18:14

2 Answers2

3

Look at the OUTPUT clause. You should be able to add it to your INSERT statement to do what you want.

BTW, if you need to output columns into the second table that weren't inserted into the first one, then use MERGE instead of INSERT (as suggested in the comment to the original question) as its OUTPUT clause supports referencing other columns from the source table(s). Otherwise, keeping it with an INSERT is more straightforward, and it does give you access to the inserted identity column.

GilM
  • 3,711
  • 17
  • 18
1

I'm having experiment to worked out in inserting multiple record into related table using databinding. So, try this!

Hopefully this is very helpful. Follow this link How to insert record into related tables. for more information.

jocodester
  • 11
  • 1