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

2 votes

1 answer

5316 views

sql-server t-sql azure-sql-database identity output-clause

                          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


                        

Asked by Riley Major (1965 rep)

May 22, 2019, 09:46 PM
Last activity: May 22, 2019, 11:02 PM

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

Related Questions