SQL MERGE

When working with tables in SQL, you will inevitably need to combine the contents of two or more tables at some point.  The SQL MERGE statement provides a quick and straight-forward way to accomplish this task.  The SQL MERGE statement performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

The following post demonstrates how to utilize the SQL MERGE statement, using a simple example to illustrate the concept.

SQL Version:  SQL Server 2014 (Express Edition)

Setup – SQL MERGE

My example will use two tables:

  1. TeamsMaster – This is the “Target” table, which is updated each year, when a new teams table is provided
  2. Teams2018 – This is the “Source” table, which is created each year for a new season

Here is the SQL code to setup and view the tables:

Tables:

SQL MERGE

SQL Merge – Example #1 – WHEN MATCHED clause

First, we will update the target table (TeamsMaster) with any matches to the source table (Teams2018), based on a JOIN on the “TeamID” field

Here is the code:

Once you execute, here is a before and after look at the TeamsMaster table.  I’ve highlighted the updates made based on the WHEN MATCHED clause.

SQL MERGE

The resulting updates were made on each row in the target table (TeamsMaster), where a matching record was found in the source table (Teams2018), based on a match on the TeamID column.  In this case, the field values outlined in RED illustrate the updates made.

 

SQL MERGE – Example #2 – WHEN NOT MATCHED BY TARGET

Next, changes will again be made to the target table (TeamsMaster) with any matches to the source table (Teams2018), based on a JOIN on the “TeamID” field.  However, we’ll add in the WHEN NOT MATCHED BY TARGET clause.  This clause will check for any records in the source table (Teams2018) that do not exist in the target table (TeamsMaster), and INSERT these records into the target table accordingly.

***Please Note:  Before running this next example, please re-run the setup code from the beginning of the post, to re-build the two sample tables to their original state

Here is the code:

Once you execute, here is a before and after look at the TeamsMaster table.  I’ve highlighted the updates made based on the WHEN MATCHED and WHEN NOT MATCHED BY TARTET clauses.

SQL MERGE

Once again, the resulting updates were made on each row in the target table (TeamsMaster), where a matching record was found in the source table (Teams2018), based on a match on the TeamID column.  Additionally, three records in the source table (Teams2018) that did not exist in the target table (TeamsMaster) were also inserted into the target based on the WHEN NOT MATCHED BY TARGET clause of the SQL MERGE statement.  The field and row values outlined in RED illustrate the changes made.

 

SQL MERGE – Example #3 – WHEN NOT MATCHED BY SOURCE

For our last example, changes will again be made to the target table (TeamsMaster) with any matches to the source table (Teams2018), based on a JOIN on the “TeamID” field.  We’ll add in the WHEN NOT MATCHED BY TARGET clause, to check for any records in the source table (Teams2018) that do not exist in the target table (TeamsMaster), and INSERT these records into the target table accordingly.  We’ll also add in the WHEN NOT MATCHED BY SOURCE clause, which checks for any records in the target table (TeamsMaster) that do not exist in the source table (Teams2018), and will DELETE these records from the target table.

***Please Note:  Before running this next example, please re-run the setup code from the beginning of the post, to re-build the two sample tables to their original state

Here is the code:

Once you execute, here is a before and after look at the TeamsMaster table.  I’ve highlighted the updates made based on the WHEN MATCHED, WHEN NOT MATCHED BY TARTET and WHEN NOT MATCHED BY SOURCE clauses.

SQL MERGE

Once again, the resulting updates were made on each row in the target table (TeamsMaster), where a matching record was found in the source table (Teams2018), based on a match on the TeamID column.  Additionally, three records in the source table (Teams2018) that did not exist in the target table (TeamsMaster) were also inserted into the target based on the WHEN NOT MATCHED BY TARGET clause of the SQL MERGE statement.  The field and row values outlined in RED illustrate the changes made.  And lastly, three records in the target table (TeamsMaster) that did not exist in the source table (Teams2018), where deleted from the target table.  The row values highlighted in ORANGE above illustrate the records deleted.

I hope you found this post informative.  Please leave a comments with any questions, or if you’d like to see any further examples of the SQL MERGE statement.

Enjoy!

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment