0

I have this query which syncs the target table with the source table. Missing rows in the target need to be inserted, changed rows (only number and name) need to be updated, and missing rows in the source need to be deleted from the target table:

MERGE Table1 AS Target
USING Table2 AS Source
    ON Source.id_no = Target.Id
    AND Source.number = Target.Number
    AND Source.[name] = Target.[Name]
WHEN NOT MATCHED BY Target THEN
    INSERT (Id, Number, [Name]) 
    VALUES (Source.id_no, Source.number, Source.[name])
WHEN MATCHED THEN UPDATE SET
    Target.Number = Source.number,
    Target.[Name] = Source.[name]
WHEN NOT MATCHED BY Source THEN
    DELETE

But the above is always updating ALL rows every time the query is executed.

What am I doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ivan Debono
  • 457
  • 3
  • 14
  • Do you have `rowversion` or "last updated time" column? If so that would make it easy. If not, you need to compare **every** column. At least you have access to `IS DISTINCT FROM`. – Thom A Jun 17 '23 at 17:26
  • No I don't have `rowversion` but I can add it to the target table. – Ivan Debono Jun 17 '23 at 17:26
  • 1
    Also, I would suggest against `MERGE`; especially when you are safeguarding against it basic flaws. See [here](//sqlblog.org/merge). – Thom A Jun 17 '23 at 17:27
  • You need the column on *both* tables; then you can see if the `rowversion` on the source matches the `binary(8)` on the target. – Thom A Jun 17 '23 at 17:27
  • Rowversion won't help. You won't be able to synch the target with the source – Martin Smith Jun 17 '23 at 17:27
  • Considering it changes when the row changes, @MartinSmith , I don't see why it wouldn't. A last updated time is preferable though. – Thom A Jun 17 '23 at 17:28
  • The source rows can change any time independent of the target. – Ivan Debono Jun 17 '23 at 17:29
  • So *both* tables can change independently? – Thom A Jun 17 '23 at 17:29
  • @ThomA - I meant because any write to the target would update the rowversion to the target - but I see you have since clarified the target would be binary rather than rowversion – Martin Smith Jun 17 '23 at 17:30
  • @ThomA Only the source table. – Ivan Debono Jun 17 '23 at 17:31
  • Then you need something on the source to indicate it has changed, @IvanDebono . – Thom A Jun 17 '23 at 17:32
  • @ThomA That would be either the `number` or `name` column or both. – Ivan Debono Jun 17 '23 at 17:32
  • Then you need to actually compare the values of those columns, @IvanDebono . An Upsert would make this better, imo. Then you can easily implement some `IS DISTINCT FROM` clauses. – Thom A Jun 17 '23 at 17:34

1 Answers1

1

You need to change

USING Table2 AS Source
    ON Source.id_no = Target.Id
    AND Source.number = Target.Number
    AND Source.[name] = Target.[Name]

To just

USING Table2 AS Source
    ON Source.id_no = Target.Id

Otherwise if, for example, a row exists on both sides and has identical id and name but different Number then you will go through to the NOT MATCHED BY Target and NOT MATCHED BY Source clauses and never get through to the UPDATE branch anyway.

With that change in place you can use

WHEN MATCHED AND Target.Number IS DISTINCT FROM Source.number OR
                 Target.[Name] IS DISTINCT FROM  Source.[name] THEN UPDATE ...

To compare the "before" and "after" column values and only update if something changed.

WHEN MATCHED AND EXISTS (SELECT Target.Number, Target.[Name] 
                        EXCEPT 
                        SELECT Source.number, Source.[name]) THEN UPDATE ...

Is more concise if you have many columns to compare

Martin Smith
  • 438,706
  • 87
  • 741
  • 845