KenL KenL - 3 months ago 8
SQL Question

Using MERGE INTO with 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
.

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

Answer

Use the inserted pseudo table in your OUTPUT clause:

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.

Comments