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

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?

From stackoverflow

• 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

  Manu : This will only retreive the identity of the last record inserted. I need to retreive all of them

  RSolberg : I'm making this a favorite question. I want to know the answer to that too :)

  RSolberg : @Manu take a look at the link I just added above...

  HLGEM : Do not ever use @@identity, it will not always return the correct value!

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

  Charles Graham : What? That's freaking sweet! When did that feature get added?

  Brian R. Bondy : I think sql 2005

  Factor Mystic : I wish I could upvote this twice, seriously

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

• 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