How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT
of an UPDATE
statement? Neither @@IDENTITY
nor SCOPE_IDENTITY()
appears to be set properly.
Consider this code:
DECLARE @UpdateTable table (UpdateTableID int IDENTITY, UpdateTableValue int);
DECLARE @InsertTable table (InsertTableID int IDENTITY, UpdateTableValue1 int, UpdateTableValue2 int);
DECLARE @TestValue int = 5;
INSERT INTO @UpdateTable (UpdateTableValue) VALUES (1),(2),(3);
SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();
INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
UpdateTableValue1, UpdateTableValue2
FROM (
UPDATE @UpdateTable
SET UpdateTableValue = UpdateTableValue + @TestValue
OUTPUT deleted.UpdateTableValue, inserted.UpdateTableValue
WHERE UpdateTableID = 2
) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);
SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();
The last-inserted row has an identity value of 1, yet the @@IDENTITY
and SCOPE_IDENTITY()
functions are returning their original values from the original INSERT
prior to the last statement executed.
@@VERSION
:
Microsoft SQL Azure (RTM) – 12.0.2000.8 May 2 2019 20:11:13
Copyright (C) 2019 Microsoft Corporation
Best Answer
I believe that this is because
@@IDENTITY
andSCOPE_IDENTITY()
do not work when used in combination with anUPDATE
statement. While I understand that the natural response to this would be "Well why doesn't it return the ID of the outer INSERT?" the answer is: because of Scope.The
UPDATE
statement, and theINSERT
statement (using the results of theOUTPUT
) are executed as one statement and are inherently within the same scope of one another. When the engine executes the query, it realises this and so it is unable to track the identity generated by the outerINSERT
. You can prove this by running the following query which splits theUPDATE
and theINSERT
into two individual statements:Which produces the correct results:
If you need to get the identity from the table you have 3 options as far as I see:
OUTPUT
clause to the finalINSERT
to capture the generated ID.IDENT_CURRENT()
function to obtain the current value.If you are using temp tables (not global temp tables) then
IDENT_CURRENT()
would be perfectly fine as you can guarantee that it will only return the value generated within your session (as the objects are not available via other sessions). However, if the example you gave is just that then I would go with option 2 and use anotherOUTPUT
clause to capture the identity value that is generated:This then outputs the correct value: