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.