Hmmm ... if I understand you correctly, this problem severely limits the usefulness of MERGE.

The WHEN NOT MATCHED clause is required for INSERTs and DELETEs, but getting all of that data in a single pass essentially requires a full table scan. That might save you time if the individual DELETE and INSERT queries require a table scan anyway. It might also be OK if the table is fairly small.

If you are trying to do a targeted UPDATE/INSERT/DELETE on a large table, though, you are better off with separate statements.

Perhaps Microsoft implemented the MERGE operator only because it is part of the ANSI standard, and they wanted to check that box and to be able to say, "Yeah, our software does that, too." How well does MERGE perform in other implementations of SQL, Oracle? Does anyone know?

This seems like a pretty fundamental limitation on an operation that is trying to everything at the same time. :ermm: