我需要知道 MERGE 语句是否执行了 INSERT。在我的场景中,插入是 0 或 1 行。
测试代码:
DECLARE @t table (C1 int, C2 int)
DECLARE @C1 INT, @C2 INT
set @c1 = 1
set @c2 = 1
MERGE @t as tgt
USING (SELECT @C1, @C2) AS src (C1, C2)
ON (tgt.C1 = src.C1)
WHEN MATCHED AND tgt.C2 != src.C2 THEN
UPDATE SET tgt.C2 = src.C2
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (src.C1, src. C2)
OUTPUT deleted.*, $action, inserted.*;
SELECT inserted.*
最后一行不编译(没有作用域,与触发器不同)。我无法访问@action 或输出。实际上,我不需要任何输出元数据。
我怎样才能做到这一点?
您可以输出到表变量中,然后从中检索。尝试这个:
DECLARE @t table (C1 int, C2 int)
DECLARE @C1 INT, @C2 INT
DECLARE @Output TABLE (DeletedC1 INT, DeletedC2 INT, ActionType VARCHAR(20), InsertedC1 INT, InsertedC2 INT)
set @c1 = 1
set @c2 = 1
MERGE @t as tgt
USING (SELECT @C1, @C2) AS src (C1, C2)
ON (tgt.C1 = src.C1)
WHEN MATCHED AND tgt.C2 != src.C2 THEN
UPDATE SET tgt.C2 = src.C2
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (src.C1, src. C2)
OUTPUT deleted.*, $action, inserted.* INTO @Output;
SELECT * FROM @Output WHERE ActionType = 'INSERT'
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)