Getting all identity values in a batch insert stat

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting all identity values in a batch insert stat

Author

Topic

junv
Starting Member

3 Posts

Posted - 2009-07-08 : 16:31:52

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.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-07-08 : 22:47:00

There is a command to get the last inserted identity column value but it only works one at a time. To do what you need, you might want to:
- insert the source data into a temp table
- copy the data into the destination table
- create a query which joins the temp table to the production table to return the ID.

It's not pretty but it should work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 14:22:33

do you have an audit column in your table? then it would be easier to retrieve the id values. However, if you want this in sql 2005, you can use the new OUTPUT operator. this is not available in 200 though.

junv
Starting Member

3 Posts

Posted - 2009-07-16 : 17:01:39

quote:
Originally posted by visakh16

do you have an audit column in your table? then it would be easier to retrieve the id values. However, if you want this in sql 2005, you can use the new OUTPUT operator. this is not available in 200 though.

I don't have an audit column. Can you explain further how it will help.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 17:20:40

It's not that hard in SQL Server 2000 either.
Give this a try

CREATE TABLE	#Sample
	(
		ID INT IDENTITY(1, 2) PRIMARY KEY CLUSTERED,
		Dummy INT NOT NULL
	)

INSERT	#Sample
SELECT	1

SELECT	SCOPE_IDENTITY() - IDENT_INCR('#Sample') * (@@ROWCOUNT - 1) AS FirstID,
	SCOPE_IDENTITY() AS LastID,
	IDENT_INCR('#Sample') AS Increment

INSERT	#Sample
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4	

SELECT	SCOPE_IDENTITY() - IDENT_INCR('#Sample') * (@@ROWCOUNT - 1) AS FirstID,
	SCOPE_IDENTITY() AS LastID,
	IDENT_INCR('#Sample') AS Increment

INSERT	#Sample
SELECT	5 UNION ALL
SELECT	6 UNION ALL
SELECT	7 UNION ALL
SELECT	8 UNION ALL
SELECT	9	

SELECT	SCOPE_IDENTITY() - IDENT_INCR('#Sample') * (@@ROWCOUNT - 1) AS FirstID,
	SCOPE_IDENTITY() AS LastID,
	IDENT_INCR('#Sample') AS Increment

DROP TABLE	#Sample

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources