当Merge into使用以下语句执行插入时,Scope_Identity返回正确的代理键信息.但是,当执行更新时,Scope_Identity和@@ Identity都会返回下一个可用的代理键.当我添加输出时,我在update和insert上都得到null.
如何在更新和插入上返回代理键？
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
)
output CurrentSet.*,@Surrogate_KEY ;
print @Surrogate_KEY
print @@IDENTITY
print SCOPE_IDENTITY()
解决方法
使用
OUTPUT clause中的inserted伪表：
DECLARE @Surrogate_KEY bigint
MERGE INTO [dbo].[MyTable] ChangeSet
USING (SELECT @NaturalKey1 AS NaturalKey1,blah
)
output inserted.* ;
这将返回语句末尾表中的值(对于受影响的行).
