Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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
