SQL Server MERGE

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the MERGE clause to incorporate the data of one table into another table with the required adjustments.

What is MERGE in SQL Server?

MERGE is a feature in SQL which allows us to incorporate the data of one table called the source table into another table called the target table. The prerequisites for a merge are that

• The source and target tables should have either the same number of columns or the target table can have more columns but not the other way round.
• The source and target table columns should be of the same datatype and ideally same data size or width. However, the target table can have a larger data size for the columns but not the other way round.

Whenever we deal with two tables, we compare their records on a condition which is usually a logical expression on a table column. Likewise, in MERGE we compare the records in the source and target tables on a primary or unique key column. This results in 3 possible outcomes and SQL deals with them in 3 different ways with 3 different clauses. The 3 different scenarios and the clauses which go with them are listed below.

▸ WHEN MATCHED – This is the simplest scenario where the key column value of a source table record matches the key column value of a target table record. In this scenario SQL updates the other column values in the corresponding target table record with values from the source table.

▸ WHEN NOT MATCHED BY TARGET – In this scenario the key column value of a source table record does not match the key column value of any of the target table records. This basically means that the source table record is an additional unique record that does not have any counterpart in the target table. SQL inserts such unique records from the source to the target table. This clause is also referred to and written as shortly in a MERGE query as WHEN NOT MATCHED.

▸ WHEN NOT MATCHED BY SOURCE – This is the last scenario in which the key column value of a target table record does not match the key column value of any of the source table records. This basically means that the target table record is an additional unique record that does not have any counterpart in the source table. SQL deletes such unique records from the target table.

The following diagram illustrates the different scenarios.

Operation

A MERGE statement in SQL is a special standalone statement with the sole purpose of replicating the data of one table (source table) to another (target table).

SQL Server MERGE Syntax

The basic syntax of the MERGE statement is given below.

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
THEN update_statement
WHEN NOT MATCHED BY TARGET
THEN insert_statement
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

In this syntax,

• MERGE – keyword to instruct SQL of a MERGE operation.
• target_table – the table which is being updated.
• source_table– the table from which the target table is being updated.
• merge_condition – the condition on which the records (rows of information) of the two tables are matched for the merge. Usually the condition is specified on a primary key or unique key column.
• WHEN MATCHED – please refer above.
• update_statement – An UPDATE statement to update the data in the target table from the source table.
• WHEN NOT MATCHED BY TARGET please refer above.
• insert_statement – An INSERT statement to add unique records from the source table to target table.
• WHEN NOT MATCHED BY SOURCE – please refer above.
• THEN DELETE – A DELETE statement to delete unique records from the target table.

SQL Server MERGE Example

To understand the above let us consider an example. Suppose we have a men’s garment business with a store which receives, reconciles, hold and distributes inventory and a shop floor where the garments are sold. We will create 2 tables in the shop database called category_stock and category_sales. The first table will contain the inventory information of all categories of garments and the second  table will contain information of the categories of garments released to and available in the shop floor for sales. The following create table queries creates the tables.

CREATE TABLE category_stock
cat_id INT, cat_name VARCHAR(50), cat_qty INT);

CREATE TABLE category_sales
(cat_id INT, cat_name VARCHAR(50), cat_qty INT);

 After creating the tables, we will insert data into the tables and have two tables as below.

cat_id	cat_name	cat_qty
119	Jeans-Regular Fit	350
120	Jeans-Slim Fit	200
121	Corduroy Pants	99
122	Formal Wrinkle Free	255
123	Khaki Pants	82
124	Designer Formals	100

cat_id	cat_name	cat_qty
120	Jeans-Slim Fit	200
121	Corduroy Pants	20
125	Jeans Torn	56
126	Jeans Faded	66

We can see that the category_stock table has more records than the category_sales table. We can also see that the two tables have 2 common categories (same cat_id) and the rest are dissimilar and that one common category ‘Corduroy Pants’ has less stock in the shop floor for sales and more stock in inventory.

Now we will do a merge from the category_stock table (i.e. source table) to the category_sales table (i.e. category_sales table) with the following query and bring the category_sales table up to date so that we have all the available stock in the inventory transferred to the shop floor for sales.

MERGE category_sales sales
USING category_stock stock
ON (stock.cat_id=sales.cat_id)
WHEN MATCHED
THEN UPDATE SET sales.cat_name=stock.cat_name, sales.cat_qty=stock.cat_qty WHEN NOT MATCHED
THEN INSERT (cat_id, cat_name, cat_qty) VALUES (stock.cat_id, stock.cat_name, stock.cat_qty)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
2> go
(8 rows affected)

After executing the query, we can check the result by running a SELECT query on the category_sales table to verify the merge. It will show the below result.

cat_id	cat_name	cat_qty
120	Jeans-Slim Fit	200
121	Corduroy Pants	99
122	Formal Wrinkle Free	255
123	Khaki Pants	82
124	Designer Formals	100
119	Jeans-Regular Fit	350

From the result we can see that the common records with same cat_id (120, 121) have been retained, unique records with different cat_id (119, 122, 123 124) from the source table have been added and unique target table records with different cat_id (125, 126) have been removed. Thus we have the entire data from the source  table (category_stock) in the target table and the target table is a replica of the source table. So, the MERGE feature and statement essentially allows us to copy data from source to target table in one go without issuing multiple INSERT, UPDATE and DELETE reducing effort and time.