Get identity value of all rows inserted

Question

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement?

Example

insert into DestinationTbl select * from SourceTbl where [col1]='xyz'

Above statement may insert more than one rows into a table. So i want identity value of all the rows which are inserted. How it can be done?

Answer 1

You can use the output clause to throw the inserted rows into a table variable. Then you'll be able to see all of the identity values.

Link for the output clause: http://technet.microsoft.com/en-us/library/ms177564.aspx