Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.