July 8, 2009 at 3:25 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 8, 2009 at 3:29 pm
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?
July 8, 2009 at 8:47 pm
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
July 8, 2009 at 8:48 pm
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
July 9, 2009 at 6:46 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply