How do I get the Identity field that results from an insert?

Question

I'm going to insert data into a table like so:

Insert Into MyTable (Field1, Field2)
           Values   ('test', 5)

When that insert occurs, the new row is going to have an identify value in the ID column. If have a variable called @ID in my T-SQL code, how do I populate @ID with the result of the T-SQL Insert?

Declare @ID int

Insert into MyTable (Field1, Field2)
             Values ('test', 5)

--//How do I populate @ID with the value of ID of the record that was just inserted?

Answer 1

There are two ways - the first is to use SCOPE_IDENTITY:

DECLARE @ID INT

INSERT INTO MyTable (Field1, Field2)
VALUES ('test', 5)

SELECT @ID = SCOPE_IDENTITY() 

Don't use @@IDENTITY -- the value it contains is for the last updated IDENTITY column without regard for scope. Meaning, it's not reliable that it holds the value for your INSERT, but SCOPE_IDENTITY does.

The other alternative is to use the OUTPUT clause (SQL Server 2005+).

Answer 2

SELECT @ID = SCOPE_IDENTITY();