0

I have a merge statement that looks like the below

...
WHEN NOT MATCHED BY TARGET then Insert
(ID,
Status, 
Read,
User,
ChangeDate)
VALUES
(Source.ID,
Source.Status,
Source.Read,
Source.User,
Source.ChangeDate)
...

I am currently using the ID, Status and ChangeDate to match source to target. Sometimes the ChangeDate can change without any actual changes being made to the row. What I need to happen is for my merge statement to run and only insert a new row if the Source.Status is not the same as the latest Target.Status based on the ChangedDate.

My current target table looks like the following

ID    Status    Read    User    ChangeDate
33    Read       Yes     76     01-Jun-2019
33    Closed     Yes     76     12-Jun-2019
33    Read       Yes     76     23-Jun-2019

My merge statement wants to insert the below row into this table

ID    Status    Read    User    ChangeDate
33    Read       Yes     76     24-Jun-2019

I don't want this to happen because although the ChangeDate has been modified, the Status is the same as the previous row (23-Jun-2019)

If my merge ran again and tried to insert the below row

ID    Status    Read    User    ChangeDate
33    Closed     Yes     76     30-Jun-2019

Then this would insert a new row as the Closed status is not the same as the previous Read status.

Appreciate any help with this, I can't think of any way of getting around it currently.

Thanks Jessica

Tried to merge on multiple different columns

Jess8766
  • 377
  • 5
  • 16

1 Answers1

0

you cant use the field ChangeDate to match the source, because of this that your merge are always insert

and about the update you can do like this:

WHEN MATCHED AND (trg.example_column <> src.example_column 
   OR trg.example_column_2 <> src.example_column_2) THEN UPDATE
   SET 
   [VERSION] = tgt.ChangeDate = GETDATE()
Alex Pereira
  • 36
  • 6