Perform Update, Delete and Insert using Merge Statement

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.

Further Reading

MERGE (Transact-SQL)