Set timestamp on MERGE

1

Hi everyone
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
USING (
SELECT
c.id
c.firstname
c.surname
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
(
[firstname],
[surname],
[updated_on],
)
VALUES
(
source.[firstname],
source.[surname],
CAST(GETDATE() as date)
)
;

Any idea how to store the timestamp in the [updated_on] field?

Thanks and best regards,
janosh

2

Have you considered this?

target.[updated_on] = 
	CASE WHEN target.[firstname] != source.[firstname]
	 OR target.[lastname] != source.[lastname]
		THEN CAST(GETDATE() As DATE) END

You will have to be careful with NULL values in the comparison also and handle those.

1 Like
3

add more conditions to the WHEN MATCHED part. Check for changes to the columns you care about

1 Like
4

Thanks for your answers. In order to have no issues with NULL values I used the following solution:

WHEN MATCHED AND EXISTS (SELECT source.firstname, source.surname EXCEPT target.firstname, target.surname) THEN UPDATE SET ...

5

I'm using a MERGE command [sic] to transfer new or updated contacts from one table to an other one (if their status [sic] is active). <<
SQL is declarative, so there are no “commands”; we have statements. This is not just SQL; it is fundamental programming concept. We also do not use a generic “status” in RDBMS. Marriage? Graduation? Employment? And we do not use assembly language flags in SQL!

A status is a state of being, so it has a temporal dimension. Please read:

Is there any way I could set a timestamp when the record [sic] has been updated on the second table? <<

Another fundamental error. Rows and records are totally different! What you are doing is transcribing notes from paper to an address book, but you are trying to mimic this in SQL.

An SQL programmer would add a new row to the Contacts history when the status changes. You have committed a design error called “attribute splitting” in which one table is split into 2 or more on one or more attribute values.