问题描述
我需要知道 MERGE 语句是否执行了 INSERT.在我的场景中,插入是 0 或 1 行.
I need to know if a MERGE statement performed an INSERT. In my scenario, the insert is either 0 or 1 rows.
测试代码:
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 或输出.实际上,我不想要任何输出元数据.
The last line doesn't compile (no scope, unlike a trigger). I can't get access to @action, or the output. Actually, I don't want any output meta data.
我该怎么做?
推荐答案
您可以将 OUTPUT 输出到表变量中,然后从中检索.试试这个:
You can OUTPUT into a table variable and then retrieve from that. Try this:
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'