In SQL Server, a better way to perform insert, update, or delete operations on a target table based on the results of a join with a source table is by using one MERGE statement.
UPDELSERT using MERGE statement
MERGE Table2 AS tgt USING (SELECT name, descr, updatedate from Table1) AS src ON src.name = tgt.name WHEN MATCHED AND src.updatedate > tgt.updatedate THEN UPDATE SET descr = src.descr, updatedate = src.updatedate WHEN NOT MATCHED THEN INSERT (name, descr, updatedate) VALUES (src.name, src.descr, src.updatedate) WHEN NOT MATCHED BY SOURCE THEN DELETE;
It’s more efficient as you’re doing just one statement instead of three individual (UPDATE, DELETE, and INSERT) SQL queries.
More examples can be found on the next posting, Ways to Upsert a Record in SQL Server.
