Getting all identity values in a batch insert statement.

• Jun Victorio

  Mr or Mrs. 500

  Points: 505

  More actions

  July 8, 2009 at 3:25 pm

  #133678

  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.

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  July 8, 2009 at 3:29 pm

  #1021978

  If you are inserting the parent table first, then the child, you'll need to lookup the parent ID as part of the child insert. I see that you are using a IDENTITY column as a surrogate key, is there another candiate key in the table?

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  July 8, 2009 at 8:47 pm

  #1022092

  In Sql 2005 you can use the OUTPUT clause to write the new identities into another table. Here is a simple example. See BOL for better ones.

  create table #hdr (a int identity(1,1), aa int)

  create table #ins (a int, aa int)

  insert into #hdr (aa) output inserted.* into #ins

  select 22

  select * from #hdr

  select * from #ins

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  July 8, 2009 at 8:48 pm

  #1022093

  In Sql 2005 you can use the OUTPUT clause to write the new identities into another table. Here is a simple example. See BOL for better ones.

  create table #hdr (a int identity(1,1), aa int)

  create table #ins (a int, aa int)

  insert into #hdr (aa) output inserted.* into #ins

  select 22

  select * from #hdr

  select * from #ins

• terrance.steadman

  SSCrazy

  Points: 2097

  More actions

  July 9, 2009 at 6:46 am

  #1022314

  Greetings,

  Another option that you might want to try is to fetch the last identity value prior to the batch insert. Then, you can use a T-SQL statement to fetch all identity values that are above the one you are holding from before the batch insert. You now have all of your new identity values.

  Have a good day.

  Terry Steadman