DbSchema | SQL Server - Use MERGE to update tables
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:
- 1st row
Banana
left intact;
- 2nd row
Apple
price changed to 25.00
;
- 3rd row
Chocolate
changed to Milk
;
- 4th row
Cake
price changed to 60.00
;
- 5th row
Peach
deleted.