How to handle the null in merge SQL's WHEN MATCHED? [duplicate]

Question

MERGE TARGET T
USING SOURCE S
ON T.ID=S.ID

WHEN MATCHED AND (S.NAME<>T.NAME OR S.DOB<>T.DOB)
THEN UPDATE
SET T.NAME=S.NAME, T.DOB=S.DOB;

The above is not able to handle situation where name/dob is null on source or target side. Because comparisons with NULL return false. How to handle this?

Example:

COALESCE(S.NAME,'')<>COALESCE(T.NAME,'')

Or

COALESCE(S.NAME,0)<>COALESCE(T.NAME,0)

Or

NULLIF(S.NAME,'')<>NULLIF(T.NAME,'')

Answer 1

• Use EXISTS( SELECT src.* EXCEPT SELECT tgt.* ) to detect if the source row differs from the target row in MERGE INTO statements.
• In T-SQL, EXCEPT does sucuessfully compare NULL values, and NULL with NOT NULL values, whereas (as you've noticed) the = and <> operators do not.
  • Another alternative is to use IS DISTINCT FROM for each column, however SQL Server does not (yet) support this feature.
• Don't forget to also specify WITH HOLDLOCK. It's very important with MERGE.

Like so:

MERGE INTO destTable WITH (HOLDLOCK) AS tgt
USING srcRelation AS src ON
    /* Only specify PK columns here: */
    tgt.Pk1 = src.Pk1
    AND
    tgt.Pk2 = src.Pk2

WHEN MATCHED AND EXISTS (

    /*
    Only specify "data" columns here; these are the columns which are also included in the UPDATE SET clause below.
    Don't include GENERATED, rowversion, PRIMARY KEY, and other "non-data" columns
    */

    SELECT tgt.Col1, tgt.Col2, tgt.Col3, etc
    EXCEPT
    SELECT src.Col1, src.Col2, src.Col3, etc
)
THEN UPDATE SET
    tgt.Col1 = src.Col1,
    tgt.Col2 = src.Col2,
    tgt.Col3 = src.Col3

WHEN NOT MATCHED BY TARGET THEN etc...;

Specifically, in your case:

MERGE INTO "target" WITH (HOLDLOCK) AS tgt
USING "source" AS src ON
    tgt."ID" = src."ID"
WHEN MATCHED AND EXISTS (
    SELECT tgt."Name"
    EXCEPT
    SELECT src."Name"
)
THEN UPDATE SET
    tgt."Name" = src."Name";