CODEWITHSUNDEEP
sqlsql-servert-sql
M. Fahmy
M. Fahmy

Reputation: 73

T-SQL MERGE with condition what is not matched?

Let's say I made a merge matched with a condition like

MERGE TargetTable WITH(HOLDLOCK) AS target
USING SourceTable AS source
ON (target.ID = source.ID)
WHEN MATCHED AND (source.BitCondition = 0) THEN
    UPDATE TargetTable
WHEN NOT MATCHED -- what is not matched??
    INSERT INTO TargetTable

Is the not matched just records not in both tables or is it like 

NOT(MATCHED AND (source.BitCondition = 0))

N.B. I intentionally want to ignore the case where it's matched and BitCondition = 1

Upvotes: 7

Views: 12149

Answers (2)

Dmitry Egorov
Dmitry Egorov

Reputation: 9650

Simply put, it's "just records not in both tables". More technically, NOT MATCHED evaluated using only the condition defined in the ON <merge_search_condition> clause.

In other words, you may consider MATCHED as an alias for the <merge_search_condition>.

So your query may be written (in pseudo code) as 

MERGE ...
ON (target.ID = source.ID)
WHEN (target.ID = source.ID) AND (source.BitCondition = 0) THEN
    UPDATE TargetTable
WHEN NOT (target.ID = source.ID) 
    INSERT INTO TargetTable

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46203

The default for NOT MATCHED according to the MERGE documentation is NOT MATCHED BY TARGET, meaning the row exists in the source but not the target of the MERGE.

To avoid inserting unmatched rows with source.BitCondition = 1, you can add an AND condition:

WHEN NOT MATCHED BY TARGET AND (source.BitCondition = 0) THEN
    INSERT INTO TargetTable

Upvotes: 8

Related Questions