Pages Menu
Categories Menu

Posted by on Mar 4, 2014 in SQL | 0 comments

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 Source and Target each with columns FirstName, LastName, and 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.*;