Search code examples
sqlsql-serversql-merge

In a SQL merge, what would be behavior in case `when not matched and <condition>` fails

Consider the following merge:

merge tgtTable tgt
using ( <record from srcTable> ) src
    on ( tgt.id = src.id )
when matched 
    and ('sha1', isnull(tgt.field, '')) != hash('sha1', isnull(src.field, ''))
then update
    set tgt.otherFields = src.otherFields
when not matched by target
then insert
    (id, field) values (src.id, src.field)

This is not a very difficult query, but I need some clarification with when matched and <condition> and the when not matched.

For example, in my source table I have an record like this:

------- SRC Table --------
ID | FIELD   | OTHERFIELDS
------------------------
5  | a_value | ...

Let's say the target table has one very similar record: 

------- TGT Table --------
ID | FIELD   | OTHERFIELDS
------------------------
5  | value_b | ...

When the merge statement runs, they are a match (tgt.id = src.id), but they will fail the and condition (('sha1', isnull(tgt.field, '')) != hash('sha1', isnull(src.field, '')).

What actually failed in the example was the and condition and not the match itself. In this case, will the insert in the not matched by target be executed?

Solution

  • No. It's exactly like with CASE clause. See example B here : https://msdn.microsoft.com/en-us/library/bb510625.aspx