2

On a SQL MERGE how do I insert a "do nothing" type statement? For example, I have this right now:

MERGE INTO dbo.Worker AS T
USING dbo.Collector_Worker AS S
ON T.WWID = S.WWID 
WHEN MATCHED THEN UPDATE SET
    T.IDSID = S.IDSID,
    ...
WHEN NOT MATCHED THEN
    INSERT (WWID, IDSID, ...)
    VALUES (S.WWID, S.IDSID, ...)
WHEN NOT MATCHED BY SOURCE THEN
     UPDATE SET T.Person_Status_Type = 'INACTIVE', T.Active = 0;

There are a ton of other columns where the ... characters are. So this works great except for the fact that it updates every row that already exists, even if there are no changes. As the table has a million rows in it, that's a ton of unnecessary modifications. I only want to update the row if a change was made.

If I try and just add an AND T.IDSID <> S.IDSID to the ON statement then it'll fail because it now tries to run the INSERT statement.

Gargoyle
  • 9,590
  • 16
  • 80
  • 145

2 Answers2

3

Actually I made this difficult. All I have to do is put the extra queries on the WHEN MATCHED and then it all works as desired, with no updates happening unless something changed.

Gargoyle
  • 9,590
  • 16
  • 80
  • 145
2

You can add an expression expression in the WHEN:

WHEN NOT MATCHED BY SOURCE AND T.IDSID <> S.IDSID THEN
     UPDATE SET T.Person_Status_Type = 'INACTIVE',
                T.Active = 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • SQL server gives an error if you try that saying `The identifier 'S.IDSID' cannot be bound. Only target columns and columns in the clause scope are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.` – Gargoyle Jan 24 '20 at 17:45