将MERGE INTO与Scope_IDENTITY一起使用

When Merge into does an insert with the following statement, Scope_Identity returns the correct surrogate key information. However when an update is performed both Scope_Identity and @@Identity return the next available surrogate key. And when I added the output, I get a null on both update and insert.

当Merge into使用以下语句执行插入时,Scope_Identity返回正确的代理键信息。但是,当执行更新时,Scope_Identity和@@ Identity都会返回下一个可用的代理键。当我添加输出时,我在更新和插入时都得到null。

How do I return the surrogate key on both the update and the 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()

2 个解决方案

#1

Use the inserted pseudo table in your OUTPUT clause:

在OUTPUT子句中使用inserted伪表:

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 whatever the values are in the table (for the affected rows) at the end of the statement.

这将返回语句末尾表中的值(对于受影响的行)。

#2

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

#1