Using OUTPUT after INSERT to get value of identity column into a (non-table value) variable

Question

Given the following simple test table:

CREATE TABLE dbo.Test
(
  Id bigint IDENTITY(1,1) NOT NULL,
  Name varchar(50) NULL
)

I would like to get the value of the identity column into a scalar variable after the INSERT using the OUTPUT clause, but this does not work:

DECLARE @InsertedId BIGINT;

INSERT INTO Test(Name) 
  OUTPUT @InsertedId=inserted.Id
  VALUES ('Michael')

-- Display resulting id for debugging
SELECT @InsertedId;
-- ...

I know I can easily do this using SCOPE_IDENTITY() after the INSERT, but is it possible to do it as part of the INSERT statement using the OUTPUT clause without resorting to a table variable?

Update, another contrived attempt that is also not legal:

-- Return the id as a result set
INSERT INTO Test(Name) 
OUTPUT inserted.Id AS TheId
VALUES ('Michael')

-- But you can't use the result set as a derived table...
SELECT TheId FROM
(
  INSERT INTO Test(Name) 
  OUTPUT inserted.Id AS TheId
  VALUES ('Michael')
)

-- ..., or you would be able to do this
SELECT TOP 1 @InsertedId=TheId
FROM
(
  INSERT INTO Test(Name) 
  OUTPUT inserted.Id AS TheId
  VALUES ('Michael')
)

Solution 1

Remember the value of the output clause is that it can return more than one record and more than one field. So you can output both the natural key and the identity for a set of data so you can also use set theory to insert multiple records into child tables. Output is very powerful and it will pay to get used to using it.

There currently is a bug in scope_identity() (see link: http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value) that Microsoft does not intend to fix. That should give you a clue as to whether you should be using output for new development even if it is a bit cludgier for single records.

Solution 2

No, this isn't possible. An OUTPUT clause can only output into a table / table variable, or be used to identify columns for composable DML (which doesn't help). SCOPE_IDENTITY() all the way, Michael.

Comments

[ZygD]

Agree. The OUTPUT clause gives a recordset which needs to go into a table: it makes no sense to assign to a scalar variable

[Andriy M]

@gbn: The recordset could be read with OPENQUERY, though. But that would require setting up the server as a linked one, of course.

[Michael Goldshteyn]

I gave you a +1 for confirming my beliefs, but HLGEM gets the checkmark for basically confirming the fact that '''only''' OUTPUT should be used for the time being.

[Will A]

+1 - that's shocking (the bug in scope_identity()), even more so as it's not going to get fixed.

[Will A]

@Michael - thanks Michael. I was unaware of the SCOPE_IDENTITY() bug - but won't forget it in a hurry - what a shocker!

[andreister]

The link about scope_identity bug isn't working for me - I found another one though where they claim there's a fix for SQL Server 2008 R2 SP1. Otherwise, as a workaround, they do recommend using output even for single records