DbSchema | SQL Server - Use MERGE to update tables

Design • 11-Apr-2019

by DbSchema Team

The easiest way to update data from one table to another in SQL Server is to use the MERGE statement. Let's consider the next example:

We have two tables: table (target) and table_updated (source). As the name suggest, the second table is an updated version of the first one. table_updated is the source of the updated data, while table is the target that has to be updated.

When we to update the table (source) with data from table_updated we'll encounter 3 scenarios:

1.table_updated has some new rows that don't exist in table. Here we have to INSERT them.

2.table_updated has some rows with the same id as table, but with updated data. Here we have to UPDATE them

3.table has some rows that don't exist anymore in table_updated. Here we have do DELETE them.

We can resolve all these 3 statements in a single query:

 MERGE table USING table_updated
 ON ( table.id = table_updated.id )
 WHEN MATCHED
    THEN update_statement
 WHEN NOT MATCHED 
    THEN insert_statement
 WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

Let's break it down to understand it better:

in the first row, we specify the target (table) and the source (table_updated);
then we provide a merge condition with ON. This condition determines how the rows from the source table are matched with those from the target. The merge condition can be set on primary keys or unique indexes;
then, we specify what will be the action in the three cases mentioned above.

MERGE example

Next, I will show you a more life-like example of the MERGE update. Let's create two tables, products and products_updated then fill them with data:

CREATE TABLE products
(
   product_id INT PRIMARY KEY,
   name VARCHAR(100),
   price MONEY
) 
GO

INSERT INTO products
VALUES
   (1, 'Banana', 15.00),
   (2, 'Apple', 20.00),
   (3, 'Chocolate', 20.00),
   (4, 'Cake', 40.00),
   (5, 'Peach', 19.00)
GO

 CREATE TABLE products_updated
  (
     product_id INT PRIMARY KEY,
     name VARCHAR(100),
     price MONEY
  ) 
  GO
  
  INSERT INTO products_updated
  VALUES
     (1, 'Banana', 15.00),
     (2, 'Apple', 25.00),
     (3, 'Milk', 25.00),
     (4, 'Cake', 60.00)
  GO

Now, let's MERGE the two tables:

MERGE products t 
    USING products_updated s
ON (s.product_id = t.product_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.name = s.name,
        t.price = s.price
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (product_id, name, price)
         VALUES (s.product_id, s.name, s.price)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

The result will be a table with: