Using The Merge Command
Performing an Upsert in SQL allows you to update or insert a row into a table. The command used will depend on whether it already exists. But what if you want to do a similar operation over a whole table? This is where the Merge command is very handy.
You can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join.
The following example assumes that we have two tables
Target each with columns
EmailAddress. We can sync the
Target table to match the
Source table with the following command.
MERGE Target AS T USING Source AS S ON (T.FirstName = S.FirstName AND T.LastName = S.LastName AND T.EmailAddress = S.EmailAddress) WHEN NOT MATCHED BY TARGET THEN INSERT([FirstName],[LastName],[EmailAddress]) VALUES(S.[FirstName],S.[LastName],S.[EmailAddress]) WHEN MATCHED THEN UPDATE SET T.[FirstName]=S.[FirstName], T.[LastName]=S.[LastName], T.[EmailAddress]=S.[EmailAddress] WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, inserted.*, deleted.*;