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

Flexible, safe and efficient solution for merging two tables

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

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).

PizzaMenu and NewPizzaMenu

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…