How do I use an INSERT statement's OUTPUT clause to get the identity value?

insert, sql, sql-server-2005

Solution

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with `.ExecuteScalar()` (instead of `.ExecuteNonQuery()`) to read the resulting `ID` back.

Or if you need to capture the newly inserted `ID` inside T-SQL (e.g. for later further processing), you need to create a table variable:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

This way, you can put multiple values into `@OutputTbl` and do further processing on those. You could also use a "regular" temporary table (`#temp`) or even a "real" persistent table as your "output target" here.

Problem

If I have an insert statement such as: ``` INSERT INTO MyTable ( Name, Address, PhoneNo ) VALUES ( 'Yatrix', '1234 Address Stuff', '1112223333' ) ``` How do I set `@var INT` to the new row's identity value (called `Id`) using the OUTPUT clause? I've seen samples of putting INSERTED.Name into table variables, for example, but I can't get it into a non-table variable. I've tried `OUPUT INSERTED.Id AS @var`, `SET @var = INSERTED.Id`, but neither have worked.

Original source