Getting SCOPE_IDENTITY() after insert with select

Question

Im having an issue getting the scope identity from an insert statement. I've done it numerous times before, but always with defined values:

INSERT INTO t_table (field1, Field2) 
VALUES (field1Val, field2Val); 
SET @id = SCOPE_IDENTITY()

How does one do so when you are getting the values for the insert by running a select from another table?

INSERT INTO t_table (field1, Field2) 
SELECT value1, value2 FROM #tempTable; 
SET @id = SCOPE_IDENTITY()

The above runs without error, but @id is null

Answer 1

Since you are trying to retrieve multiple values here you need to use the OUTPUT clause. It will return all the newly inserted values into a table or table variable.

https://msdn.microsoft.com/en-us/library/ms177564.aspx