2

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

2

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:

enter image description here

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:

enter image description here

  • Thanks for the detailed answer. Unfortunately: (1) I don't think you can use IDENT_CURRENT() on a table variable. (2) I can't do a second OUTPUT because I need to capture the value and use it later and you can't do an OUTPUT INTO in this case because you get this error: The OUTPUT INTO clause is not allowed when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. In the end I did work around it using your first suggestion. Not ideal as I wanted the INSERT to be in the order of the UPDATEs. (Same statement should make that very likely if not guarantee.) – Riley Major May 22 at 22:54
  • You are correct, IDENT_CURRENT cannot be used on table variables - not sure why I said that - I have removed it from the answer. If you are able to though, you could switch to temp tables in which case it can be used. – Mr.Brownstone May 22 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.