Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
junv
Starting Member
3 Posts |
 Posted - 2009-07-08 : 16:26:10
|
Hi there,I have a table that has an identiy column as the primary key. My question is how do I get/retrieve all the identity values from my batch insert statement. I need this because we have a header-detail tables. I need all the header identity values as a foreign key when I do the batch insert for my detail table. I have to use t-sql and not store procedure or trigger. Also, it would be nice if it works for both sql 2000/2005 because we both support/use these product. Another thing is I'm using a java jdbc driver to execute this batch statements.Your help is very much appreciated. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-08 : 23:30:01
|
| if I understand correctly, what you're doing is a dangerous thing and will at some point cause corruption/invalid data. you're creating a race condition by inserting records into different dependant tables in seperate batches. You need to create a stored proc to do this all in one batch, and you need to create foreign keys from the detail table to the header table.That said, what you're looking for is the @@identity function or scope_identity(). Have a look at them in BOL.Again, I can't stress enough how imprtant it is to make these inserts atomic -- both succeed or both fail -- and to be certain that the detail records will always match the correct header record. |
 |
|
|
|
|
|
Getting all identity values in a batch insert stat