在这种情况下,为什么我需要 SQL 合并的“匹配"部分? [英] Why do I need the 'match' part of a SQL merge, in this scenario?

查看:90
本文介绍了在这种情况下,为什么我需要 SQL 合并的“匹配"部分?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

考虑以下事项:

merge into T t1
using (select ID,Col1 from T where ID = 123) t2
on 1 = 0
when not matched then insert (Col1) values (t2.Col1);

来自编程背景,对我来说这意味着:

Cominig from a programming background, to me this translates to:

评估false(即1 = 0),当它是false(即一直)时,插入."

"Evaluate false (i.e. 1 = 0), and when it is false (i.e. all the time), insert."

是不是可以省略匹配条件?是不是因为我选择的 where 条件让我在这里感到困惑?是否应将此条件移至 on?

Is it not possible to just omit the match condition? Is it because of my select's where condition that I'm confused here? Should this condition be moved to the on?

注意:

由于output 的限制,我不能使用insert.由于超出我所要求范围的原因,我需要将此合并的结果输出到临时表中.

Due to restrictions with output, I cannot use insert. I need to output the results of this merge into a temporary table for reasons outside of the scope of what I'm asking.

推荐答案

您在评论中链接的答案,正如我希望清楚的那样,我们正在滥用 MERGE 语句.

In the answer you've linked to in the comments, as I've hopefully made clear, we are abusing the MERGE statement.

您在此处显示的查询可以简单地替换为:

The query you've shown here could trivially be replaced by:

insert into T(Col1) select Col1 from T where ID = 123

但是,如果您希望能够添加 OUTPUT 子句,并且那个 OUTPUT 子句需要同时引用新插入的数据来自源表的数据,你不能写INSERT 语句中的此类子句.

However, if you want to be able to add an OUTPUT clause, and that OUTPUT clause needs to reference both the newly inserted data and data from the source table, you're not allowed to write such a clause on an INSERT statement.

因此,我们改为使用 MERGE 语句,但不是出于其预期目的.整个目的是强制它执行 INSERT 并编写我们的 OUTPUT 子句.

So, we instead use a MERGE statement, but not for its intended purpose. The entire purpose is to force it to perform an INSERT and write our OUTPUT clause.

如果我们检查 MERGE,我们看到我们可以指定执行 INSERT 的唯一子句是在 WHEN NOT MATCHED [BY TARGET] 子句中 - 在两个 INSERT 子句中>WHEN MATCHED 和 WHEN NOT MATCHED BY SOURCE 子句,我们唯一的选择是 UPDATEDELETE.

If we examine the documentation for MERGE, we see that the only clause in which we can specify to perform an INSERT is in the WHEN NOT MATCHED [BY TARGET] clause - in both the WHEN MATCHED and WHEN NOT MATCHED BY SOURCE clauses, our only options are to UPDATE or DELETE.

所以,我们必须编写 MERGE 使得匹配总是失败 - 最简单的方法是说当 1 = 01 - 希望永远不会.

So, we have to write the MERGE such that matching always fails - and the simplest way to do that is to say that matching should occur when 1 = 01 - which, hopefully, is never.

1因为 SQL Server 不支持 布尔文字

1Since SQL Server doesn't support boolean literals

这篇关于在这种情况下,为什么我需要 SQL 合并的“匹配"部分?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆