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')
)

Answer 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.

Answer 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.