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

SELECT @@IDENTITY

This is how I've done it before. Not sure if this will meet the latter half of your post though.

EDIT
Found this link too, but not sure if it is the same...
http://stackoverflow.com/questions/95988/how-to-insert-multiple-records-and-get-the-identity-value

Answer 2

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 3

@@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.

Answer 4

As far as I know, you can't really do this with straight SQL in the same script. But you could create an INSERT trigger. Now, I hate triggers, but it's one way of doing it.

Depending on what you are trying to do, you might want to insert the rows into a temp table or table variable first, and deal with the result set that way. Hopefully, there is a unique column that you can link to.

You could also lock the table, get the max key, insert your rows, and then get your max key again and do a range.

Trigger:

--Use the Inserted table.  This conaints all of the inserted rows.
SELECT * FROM Inserted

Temp Table:

insert field1, unique_col into #temp from tbl_abc

insert into tbl_xyz (field1, unique_col) select field1, unique_col from tbl_abc

--This could be an update, or a cursor, or whatever you want to do
SELECT * FROM tbl_xyz WHERE EXISTS (SELECT top 1 unique_col FROM #temp WHERE unique_col = tbl_xyz.unique_col)

Key Range:

Declare @minkey as int, @maxkey as int

BEGIN TRANS --You have to lock the table for this to work

  --key is the name of your identity column
  SELECT @minkey = MAX(key) FROM tbl_xyz
  insert into tbl_xyz select field1 from tbl_abc
  SELECT @maxkey = MAX(key) FROM tbl_xyz

COMMIT Trans

SELECT * FROM tbl_xyz WHERE key BETWEEN @minkey and @maxkey