MERGE doesn't allow you to have different sets of columns for comparing a match and for updating

Frank_Lin

I need to merge new/updated records from a temp table into a staging table. Each row has a DateTime column that signals when the row was copied. I need MERGE to compare all non-primarykey column values (excluding the DateTime column) and only update the staging table if those non-primarykey column values are different. When the update happens though, the DateTime column value should also be updated. I can do this with an UPDATE and INSERT but they are probably not as efficient as MERGE. This is an example: UPDATE STAGINGTABLE SET DEST.NONPKCOL1 = TEMP.NONPKCOL1, DEST.DATETIMECOL = TEMP.DATETIMECOL -- DATETIMECOL is updated if an update is required FROM TEMPTABLE AS TEMP, STAGINGTABLE AS DEST WHERE TEMP.PKCOL = DEST.PKCOL AND (DEST.NONPKCOL1 <> TEMP.NONPKCOL1) -- DATETIMECOL is not used in the comparison

[Deleted User]

Hi Frank, thanks for reporting your problem. We'll let you know if there is an efficient way to do this using the MERGE function.

Frank_Lin

Thanks Danielle!

Massimo_Loporch

MERGE INTO STAGINGTABLE AS DEST USING TEMPTABLE AS TEMP ON TEMP.PKCOL = DEST.PKCOL AND (DEST.NONPKCOL1 <> TEMP.NONPKCOL1) WHEN MATCHED THEN UPDATE SET NONPKCOL1 = TEMP.NONPKCOL1, DATETIMECOL = TEMP.DATETIMECOL WHEN NOT MATCHED THEN INSERT (....) VALUES (...);