DbSchema Database Designer

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.

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.