July 8, 2009 at 2:01 pm
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.
July 20, 2009 at 5:27 am
On SQL Server 2005 (and later) you can use the OUTPUT clause of the INSERT statement to retrieve the identity values.
Books Online article:
http://msdn.microsoft.com/en-us/library/ms174335(SQL.90).aspx
Look at example K.
On SQL 2000 your best option would be to use another (i.e. "natural") key to correlate the affected data set with the values in the destination table after the insert operation. The identity column is not the only key, is it?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
July 20, 2009 at 6:15 am
Hi
I was also looking for the same problem....
Thanks a lot....
But is there any way to output the values into Variables than into the Table?
July 20, 2009 at 6:20 am
You can output the values into a table variable. What exactly are you trying to achieve?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
July 20, 2009 at 9:49 pm
hi
I want to get the identity column value after insert.....
Can i get this with an int variable....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply