Sunday, April 8, 2012

Get all identity values from using insert into select

use the OUTPUT clause

For Example we are going to insert the data's from the Table2 to Table 1 and get the Inserted Identity values ,

we can use the following Syntex

--------------------------------------------------------------------

Declare @IDTable Table(ID int);

INSERT into Table1(Col1, col2,....)
Output INSERTED.ID Into @IDTable(ID)
Select (Col1, col2,....)
From Table2
--------------------------------------------------------------------

All the Identity values are stored in the @IDTable Table variable.


SELECT * from @IDTable

:)