SQL Server 2008 Merge Statement Multiple Match Conditions

Question

I am attempting to bring in claim data in an incremental manner. I am matching source and target on System (varchar) and ClaimNum (varchar), with a hash of the other columns to check for changes.

I have my merge statement (simplified):

MERGE target
USING source ON target.System = source.System
             AND target.ClaimNum = source.ClaimNum

WHEN MATCHED AND target.HashValue <> source.HashValue
THEN {update claim record data}

WHEN MATCHED AND target.HashValue = source.HashValue
THEN {update claim record as "checked"}

WHEN NOT MATCHED
THEN {insert new claim record}

However, I can't have 2 matched conditions. How else can I accomplish this with a merge statement?

This is being used on SQL Server 2008.

Answer 1

You could use CASE or IIF(SQL Server 2012) to add custom logic:

MERGE target
USING source
  ON target.System = source.System
 AND target.ClaimNum = source.ClaimNum
WHEN MATCHED THEN 
   UPDATE SET column_name = 
            CASE WHEN target.HashValue = source.HashValue THEN ...
                 ELSE ...
            END
WHEN NOT MATCHED THEN {insert new claim record}

EDIT:

If it is only UPDATE you could use multiple conditions/set to the same value to skip:

...
UPDATE SET column_name1 = 
            CASE WHEN condition1 THEN ...
                 ELSE column_name1
            END
           ,column_name2 = 
             CASE WHEN condition1 THEN ...
                  WHEN condition2 THEN ...
                  ELSE column_name2
             END
           ,...

---

Note:

If source/target HashValue is nullable you should handle it using COALESCE/ISNULL/adding OR target.HashValue IS NULL.... Comparison operators don't work with NULL.