SQL — insert, delete and update in ONE statement: sync your tables with MERGE

Mike Huls

May 21, 2021 — 5 min read

This process can only handle one table at a time; we have to merge (Image by Roger Bradshaw on Pexels)

Flexible, safe and efficient solution for merging two tables

With a MERGE you can can ‘sync’ two tables by executing an insert, delete and update in ONE statement. A MERGE is much more than that though; it offers you a wide range of options in comparing and syncing tables. You can even keep track of the output of the merge. We’ll get into all of them in this article. The main reason for a merge:

It’s Atomic; it either executes all commands (e.g. update, insert and delete) or none. If either one of the commands fails it rolls back everything.
Efficient and fast: SQL Server needs to compare records once
A lot of flexibility
Impressing your manager and coworkers

1 Setup: create some tables with data

Imagine we have a startup pizza company. We keep track of the pizza’s in a table called Pizzamenu. Every week we receive a new dataset and that contains the newest menu. Our goal is to update info about existing pizza’s, add new ones and remove old ones. Let’s first create the tables and insert some data.

In these queries you can see that both the PizzaMenu and the NewPizzaMenu are almost identical. The only difference is that PizzaMenu contains an extra column that indicates whether or not the pizza is on the menu or not (IsDeleted).

As you can see the new menu doesn’t contain the Pizza Hawaii anymore. Also The name for first pizza and the price for Tonno is fixed. In addition two pizza’s are added. Let’s find out how to do this.

2 The Merge Query

A merge query compares a Target and a Source table. The Target table is your source of the truth, the Source will be used to enrich the target. Check out the diagram below.

Matching a Source and Target table (Photo by Author)

The comparison takes place based on multiple columns and reminds one of a JOIN. Think of the MERGE as a sort of JOIN; it compares the records of two tables. In stead of a LEFT, INNER and RIGHT join we can think of the MERGE as bein able to detect and handle records that match ‘left’, ‘inner’ and ‘right’. Check it out our query:

In a merge we merge a Target table with a Source tabel. The Target tabel is the source of the truth. In the first block of our query we define what our Source (newPizzaMenu) and Target (PizzaMenu) will be. Like in a JOIN we’re defining which column values have to match in order for records to be matched. In this case ‘PizzaId’ and ‘Size’.

— new records (‘right match’)
The second block handles what happens when there are new records in the Source that are not yet present in our Target. In our example this is the case for two new pizza’s: Calzone L and XL. In this block we’ve decided to insert the new records into the Target (PizzaMenu).

— matched records (‘inner match’)
This part represents the center of the Venn diagram; a sort of INNER JOIN between our Source and Target tables. In our case the matching records are the Pepperoni and Tonno pizza. In this part we are updating the Name, Price and Modified.

— deprecated records (‘left match’)
Handles records that exist in the Target but not in the Source. This represents records that are present in our current menu but not in the new menu, in our example this is represented by the pizza Hawaii. We’ve decided to not delete this record (it’s commented out), instead we update the IsDeleted column in the Target (our current menu).

When we execute the query we see:

Exactly what we wanted! Notice that:

red: from ‘right match’ → pizza tonno is taken off the menu
orange: updated: corrected the price and name of the fist two pizza’s
green: added the bottom two records

3 Flexibility

You don’t have to update records that appear in the WHEN MATCHED part; feel free to do whatever you want. The merge just detects whether the records match ‘left’, ‘right’ or ‘inner’. Some examples:

only update matching records and insert new records; don’t delete anything
Insert a record in an error-table if it appears in the left or right match
Delete all matching records from the Target and only insert Right-matching (new) records

Also there’s some flexibility in selecting matching records for example. Try the code below that updates matched records’ name with a special limitation.

WHEN MATCHED AND target.Name == ‘Hawaii’
THEN Target.Name = ‘The forbidden one’
WHEN MATCHED AND target.stock != ‘Hawaii’
THEN Target.Name = Source.Name
This updates the stock only if the new stock is a positive number

Let’s now really get our hands dirty with some bonus uses (Image by Cottonbro on Pexels)

Bonus: check out the output of the MERGE

While executing the merge we can also output all the changes it made. To demonstrate this we adjust the query a little bit. The only change is that we don’t update in the Right Match, we now fully delete it. Check out the query below.

With the last 5 records we’re going to output what action we performed and exactly what we changed:

In the first column you see what action we’ve performed, Then, in the 2 until 9 you’ll see information about the records we’ve inserted. The last 8 columns you’ll see information about deleted records. Obviously inserted records have no ‘inserted’ information and deleted records have no ‘inserted’ information. Records that are updated have both.

Bonus2: insert the output of the MERGE into a logging table

You can use the output of a merge and keep track of how your inventory changes over all new data deliveries. Lets create a table called PizzaMenuhistory.

Now we can, in ONE transaction, MERGE two tables (update, insert and delete) and delete the output of the merge into the history table:

Now check out this little table PizzaMenuHistory:

A nice history of changes to our PizzaMenu

Conclusion

In conclusion: a merge offers a flexible, safe and efficient solution for merging two tables. Using it’s output you can also easily keep track of all changes. Also check out:

Now go forth and optimize!! Happy coding!

— Mike

P.S: like what I’m doing? Follow me!