10

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') )
Improve this question

2 Answers 2

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 - that's shocking (the bug in scope_identity()), even more so as it's not going to get fixed.
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
6

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.

Improve this answer

4 Comments

Agree. The OUTPUT clause gives a recordset which needs to go into a table: it makes no sense to assign to a scalar variable
@gbn: The recordset could be read with OPENQUERY, though. But that would require setting up the server as a linked one, of course.
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.
@Michael - thanks Michael. I was unaware of the SCOPE_IDENTITY() bug - but won't forget it in a hurry - what a shocker!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.