I'm using a MERGE command to transfer new or updated contacts from one table to an other one (if their status is active). Is there any way I could set a timestamp when the record has been updated on the second table? Currently it always updates the timestamp regardless if firstname/surname has changed or not.
(Please note, in the real table there are a lot more columns than just first/surname.)
This is the current statement:
MERGE contacts2 AS target
FROM contacts1 c
WHERE c.status = 'active'
) AS source
ON (target.id = source.id)
WHEN MATCHED THEN UPDATE SET
target.[firstname] = source.[firstname],
target.[surname] = source.[surname],
target.[updated_on] = CAST(GETDATE() as date) -- <-- this should only be set if there is any change to firstname or surname
WHEN NOT MATCHED BY TARGET THEN INSERT
CAST(GETDATE() as date)
Any idea how to store the timestamp in the [updated_on] field?
Thanks and best regards,