CODEWITHSUNDEEP
sql-servert-sqlscope-identity
Digital Fusion
Digital Fusion

Reputation: 165

Getting SCOPE_IDENTITY() after insert with select

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

Upvotes: 1

Views: 6752

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 6

Related Questions