Geek Answers Handbook

Using MERGE INTO with Scope_IDENTITY

When Merge into performs insert with the following expression, Scope_Identity returns the correct surrogate key information. However, when a update is performed on both Scope_Identity and @@Identity , it returns the next available surrogate key. And when I added output , I get zero on both update and insert .

How to return a surrogate key for both update and insert ?

 DECLARE @Surrogate_KEY bigint MERGE INTO [dbo].[MyTable] ChangeSet USING (SELECT @NaturalKey1 AS NaturalKey1, @NaturalKey2 AS NaturalKey2, @NaturalKey3 AS NaturalKey3, @Surrogate_KEY AS Surrogate_KEY) CurrentSet ON ChangeSet.NaturalKey1 = CurrentSet.NaturalKey1 AND ChangeSet.NaturalKey2 = CurrentSet.NaturalKey2 AND ChangeSet.NaturalKey3 = CurrentSet.NaturalKey3 WHEN MATCHED THEN UPDATE SET blah, blah, blah WHEN NOT MATCHED THEN INSERT VALUES ( blah, blah, blah ) output CurrentSet.*, @Surrogate_KEY ; print @Surrogate_KEY print @@IDENTITY print SCOPE_IDENTITY()
+7
source share
2 answers

Use the inserted pseudo-table in the OUTPUT section:

 DECLARE @Surrogate_KEY bigint MERGE INTO [dbo].[MyTable] ChangeSet USING (SELECT @NaturalKey1 AS NaturalKey1, @NaturalKey2 AS NaturalKey2, @NaturalKey3 AS NaturalKey3, @Surrogate_KEY AS Surrogate_KEY) CurrentSet ON ChangeSet.NaturalKey1 = CurrentSet.NaturalKey1 AND ChangeSet.NaturalKey2 = CurrentSet.NaturalKey2 AND ChangeSet.NaturalKey3 = CurrentSet.NaturalKey3 WHEN MATCHED THEN UPDATE SET blah, blah, blah WHEN NOT MATCHED THEN INSERT VALUES ( blah, blah, blah ) output inserted.* ;

This returns the values ​​that are in the table (for the affected rows) at the end of the statement.

+8
source 
 DECLARE @Id ... -- MERGE dbo.Table AS Tgt USING ( SELECT <Keys> ) AS Src ON Src.<Keys> = Tgt.<Keys> WHEN MATCHED THEN UPDATE SET <...> ,@Id = Tgt.Id WHEN NOT MATCHED THEN INSERT ( ... ) VALUES ( ... ) ;-- RETURN/SET/PRINT ISNULL(@Id, SCOPE_IDENTITY())
+4
source

More articles:


All Articles