Getting all identity values in a batch insert statement.

Question

Getting all identity values in a batch insert statement.

Jun Victorio

Mr or Mrs. 500

Points: 505
More actions
July 8, 2009 at 2:31 pm

#75498

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there is an optional OUTPUT clause you can use to retrieve all the values that were inserted; typically you insert the output into a temp table and use it for further processing; see books online for some good examples...i'm pretty sure it was around for the 2000 syntax as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lowell (7/8/2009)
there is an optional OUTPUT clause you can use to retrieve all the values that were inserted; typically you insert the output into a temp table and use it for further processing; see books online for some good examples...i'm pretty sure it was around for the 2000 syntax as well.

The OUTPUT clause was added in SQL Server 2005.

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 3

Ok So I'm sure some one will send an assasin my way for saying this, but:

Cursors are not always evil, and will certainly help in this instance!

You can then simply return them one by one.

How many records do you expect to have in this record set?

Performance will still be in acceptable ranges if count(records) <= 1000 for instance.

Over a thousand records your user might have to wait a couple of seconds.

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 4