How can I retrieve the identities of rows that were inserted through insert...select?

Question

I am inserting records through a query similar to this one:

insert into tbl_xyz select field1 from tbl_abc

Now I would like to retreive the newly generated IDENTITY Values of the inserted records. How do I do this with minimum amount of locking and maximum reliability?

Answer 1

You can get this information using the OUTPUT clause.

You can output your information to a temp target table or view.

Here's an example:

DECLARE @InsertedIDs TABLE (ID bigint)
INSERT into DestTable (col1, col2, col3, col4)
OUTPUT INSERTED.ID INTO @InsertedIDs
SELECT col1, col2, col3, col4 FROM SourceTable

You can then query the table InsertedIDs for your inserted IDs.

Answer 2

@@IDENTITY will return you the last inserted IDENTITY value, so you have two possible problems

1. Beware of triggers executed when inserting into table_xyz as this may change the value of @@IDENTITY.

2. Does tbl_abc have more than one row. If so then @@IDENTITY will only return the identity value of the last row

Issue 1 can be resolved by using SCOPE__IDENTITY() instead of @@IDENTITY Issue 2 is harder to resolve. Does field1 in tbl_abc define a unique record within tbl_xyz, if so you could reselect the data from table_xyz with the identity column. There are other solutions using CURSORS but these will be slow.