Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Retrieving Identities on set-based inserts

By Steve McRoberts,

A common situation where one is tempted to use cursors is this: You have a table with an identity column. You need to insert multiple rows to this table, then use the identities for further processing, such as inserting rows with those values into a child table.

The cursor method involves inserting the rows one at a time so you can access the @@identity value for use in inserting to the child table. This works as long as there are no triggers on the table that insert rows into other tables, which themselves contain identity columns . In that case the @@identity value will be useless. I know of a system that does exactly that as it logs inserts to a log table that has an identity column on it. Attempting to work-around this issue by selecting the max(identity-column) is only a reasonable idea if you are the sole process inserting to the table (and there is no likelihood of that fact ever changing.)

An attempt at a set-based approach is to do the insert with the TABLOCKX hint and then subtract @@rowcount-1 from @@identity to get the range of identities that were inserted. However, I'm leery of this approach since hints are not commands: there's no guarantee that some other process didn't insert a row during our insert, skewing our range. Also, it is liable to the same potential trigger issue noted above. And, of course, locking the entire table may not be desirable.

In SQL Server 2005 we have the perfect solution. It will work regardless of triggers, and it will eliminate the need to do the inserts one at a time. It is the new OUTPUT clause available on the Insert, Update and Delete statements.

The OUTPUT clause lets you output data from the INSERTED or DELETED virtual tables which were formerly only accessible from within a trigger. In our case, we simply need to preserve the identity column (in a real-life example you would preserve any other column data you needed).

DECLARE @newID TABLE(new_ID INT) -- Generate 100 rows of test data
;WITH mycte AS
(SELECT 100 AS seedValue
UNION ALL SELECT seedValue + 1 FROM mycte WHERE seedValue + 1 < 200
) INSERT INTO @parent(myCol)
SELECT * from mycte SELECT * FROM @newID

The important part of this very simple example is:


In the end, the @newID table variable will contain 100 new ID's guaranteed to be from the rows we just inserted into the @parent table variable.


Total article views: 4116 | Views in the last 30 days: 2
Related Articles

Inserting zero in Identity Column

Inserting zero in Identity Column


identity column

identity column


output behavior

delete output into identity column


Zero being inserted in identity column

Zero being inserted in identity column


Insert from with an identity column

Difficulty with identity column


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones