How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?

Question

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

Answer 1

I believe that this is because @@IDENTITY and SCOPE_IDENTITY() do not work when used in combination with an UPDATE 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 the INSERT statement (using the results of the OUTPUT) 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 outer INSERT. You can prove this by running the following query which splits the UPDATE and the INSERT into two individual statements:

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();

DECLARE @tmp TABLE 
(
    UpdateTableValue1 int, 
    UpdateTableValue2 int
);

UPDATE      @UpdateTable
SET         UpdateTableValue = UpdateTableValue + @TestValue
OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
INTO        @tmp
WHERE       UpdateTableID = 2;

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        @tmp;

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

Which produces the correct results:

If you need to get the identity from the table you have 3 options as far as I see:

1. Use the technique I use above to obtain the value.
2. Add an OUTPUT clause to the final INSERT to capture the generated ID.
3. Use the 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 another OUTPUT clause to capture the identity value that is generated:

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
OUTPUT inserted.InsertTableID
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        
(
    UPDATE      @UpdateTable
    SET         UpdateTableValue = UpdateTableValue + @TestValue
    OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
    WHERE       UpdateTableID = 2
) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);

This then outputs the correct value: