I recently came across the problem of how to get the identity value of records INSERTED. I found out the solution is using OUTPUT, which is really powerful.

My issue was that I was trying to insert data into MyTable1 from MyTable2, where I didn’t know what the value was after the insert into MyTable1.

The solution was the following code:

INSERT INTO MyTable1( Column2, Column3, Column4)
OUTPUT INSERTED.Column1
SELECT Column2, Column3, Column4
FROM MyTable2 WHERE Column2 = 'Unique-Value-Here';


Another option is that you can create a table variable and hold the content to be used later.


DECLARE @MyOutputTable TABLE (Column1 INTEGER);

INSERT INTO MyTable1( Column2, Column3, Column4)
OUTPUT INSERTED.Column1 INTO @MyOutputTable(Column1)
SELECT Column2, Column3, Column4
FROM MyTable2 WHERE Column2 = 'Unique-Value-Here';


This way you can look up the identity values stored in the table variable for more than 1 record at a time.