Why Do I Need The 'match' Part Of A Sql Merge, In This Scenario?

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 b

Solution 1:

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 intoT(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 = 0 - which, hopefully, is never.

---

Since SQL Server doesn't support boolean literals