0

Consider the following:

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:

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

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?

NOTE:

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.

  • That's a very odd use of `merge`. It's not at all clear why they've not just written `insert into T(Col1) select Col1 from T where ID = 123` which should have exactly the same effect. – Damien_The_Unbeliever Jul 21 '15 at 10:14
  • 1
    @Damien_The_Unbeliever - haha ironically, it was you who wrote it (minus the `where`)! But I left out probably the most important bit of context as to why `merge` was used instead of `insert`, which is because of the need for `OUTPUT` to a temp table: http://stackoverflow.com/a/16331659/1017882 I added the `where` as I wanted to use a parameter to determine which records to merge. This isn't my actual code, it's for example's sake (edit). –  Jul 21 '15 at 10:16

1 Answers1

4

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

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.

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.

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.

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.

1Since SQL Server doesn't support boolean literals

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thanks very much! I understand now, I was completely misunderstanding the purpose of the match. Thanks again. –  Jul 21 '15 at 10:26