DbSchema | SQL Server - How to Use Merge Statement?

Publish on DbSchema Blog >>>

SQL Server: How to Use Merge Statement in sqlcmd and DbSchema

Table of Contents

• Introduction
• Prerequisites
• Understanding the Merge Statement
  • Purpose of Using Merge Statement
  • Restrictions and Permissions Required
  • Advantages and Limitations
  • Use Cases
• Using Merge Statement in sqlcmd
• Using Merge Statement in DbSchema
• Conclusion
• References

Introduction

SQL Server, a relational database management system developed by Microsoft, offers a plethora of tools and features that enable effective data management. Among these, the Merge statement stands out due to its unique functionality and versatility. This article aims to explore the Merge statement, its purpose, usage, advantages, and limitations, while also detailing its implementation in the sqlcmd and DbSchema environments.

Prerequisites

To follow this article, you should have:

1. A basic understanding of SQL and SQL Server.
2. SQL Server installed on your system.
3. Basic knowledge of sqlcmd utility and DbSchema.

For installation and establishing connection you can read our article SQL Server-How to create a database?

Understanding the Merge Statement

The Merge statement in SQL, introduced in SQL Server 2008, allows you to perform multiple DML operations (INSERT, UPDATE, DELETE) in a single statement, based on a source table and a target table. It enables the synchronization of these two tables by comparing them using a certain criteria.

---

Purpose of Using Merge Statement

The primary purpose of using the Merge statement is to modify data in a target table based on a source table. This helps in managing complex synchronization scenarios more easily. It’s extremely useful when you need to perform multiple types of modifications in one operation.

Restrictions and Permissions Required

To execute a Merge statement, you need to have INSERT, UPDATE, DELETE, and SELECT permissions on the target table, and SELECT permission on the source table. As for restrictions, the Merge statement must be terminated by a semi-colon (;), and the total number of table and column references in the Merge statement cannot exceed 256.

Advantages and Limitations

Advantages:

1. Consolidates multiple operations into one statement, which can result in improved performance.
2. Handles complex synchronization tasks easily.
3. Provides explicit control over the operation.

Limitations:

1. The Merge statement might have lower performance for larger datasets compared to separate SQL statements.
2. It can be more complex to write and understand than individual DML statements.
3. It requires the necessary permissions on both source and target tables.

Use Cases

Merge statements are frequently used in scenarios such as:

• Data warehousing: Where the data is periodically moved from an operational database to a warehouse.
• Incremental updates: Where data from a source table is used to update and synchronize the target table.

Here is an example of how a Merge statement might look:

MERGE Target AS T
USING Source AS S
ON (T.ID = S.ID)
WHEN MATCHED THEN 
    UPDATE SET T.Name = S.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name) VALUES (S.ID, S.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

In the above example, the Target table is synchronized with the Source table based on the ID column. When a match is found, it updates the Name in the Target table, if not, it inserts the new record from Source to Target. If a record in Target does not have a matching record in Source, it gets deleted.

Using Merge Statement in sqlcmd

sqlcmd is a command-line tool for SQL Server. It allows SQL queries to be written and executed from the command prompt or from a script file. Here’s how to use the Merge statement using sqlcmd:

1. Open command prompt.

2. Connect to your SQL Server instance by typing the following command:

   sqlcmd -S ServerName -U UserName -P Password -d DatabaseName
   

   Replace ServerName, UserName, Password, and DatabaseName with your actual SQL Server details.

3. You should now be in the sqlcmd command prompt. Here, write the Merge statement you want to execute. For example, let’s assume we have two tables, Products and UpdatedProducts.

Initial Tables

Let’s assume we have two tables, Products and UpdatedProducts with the following records:

Products
ProductID	ProductName	Price
1	Apple	1.00
2	Banana	0.50
3	Cherry	2.00

UpdatedProducts
ProductID	ProductName	Price
2	Banana	0.60
3	Cherry	2.10
4	Durian	3.00

We want to merge the UpdatedProducts into Products, updating existing prices and product names, deleting products not in UpdatedProducts, and adding new products from UpdatedProducts.

MERGE Products AS T
USING UpdatedProducts AS S
ON (T.ProductID = S.ProductID)
WHEN MATCHED THEN 
    UPDATE SET T.ProductName = S.ProductName, T.Price = S.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price) VALUES (S.ProductID, S.ProductName, S.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

4. After executing the statement, SQL Server will provide a message showing the number of affected rows.

Message
(4 row(s) affected)

This includes 2 updates (Banana and Cherry), 1 delete (Apple), and 1 insert (Durian).

The ‘Products’ table after the operation:

Products
ProductID	ProductName	Price
2	Banana	0.60
3	Cherry	2.10
4	Durian	3.00

As we can see, the Products table now reflects the current products and prices from the UpdatedProducts table.

Using Merge Statement in DbSchema

DbSchema is a GUI tool for database management. Here’s how to use the Merge statement in DbSchema:

1. Start DbSchema and connect to your database. This can be done via the Connect option in the toolbar.
2. Open the SQL Editor. This can be done from the toolbar or by right-clicking on the database in the left-side tree and selecting Open SQL Editor.
3. In the SQL Editor, write your Merge statement. For instance, let’s assume we have two tables, Customers and NewCustomers.

    MERGE Customers AS T
    USING NewCustomers AS S
    ON (T.CustomerID = S.CustomerID)
    WHEN MATCHED THEN 
        UPDATE SET T.CustomerName = S.CustomerName, T.Country = S.Country
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerID, CustomerName, Country) VALUES (S.CustomerID, S.CustomerName, S.Country)
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE;

4. Click the Run button or press F5 to execute the statement.

The Query Results window will show the results of the execution in tabular format. You will see the affected rows in the Messages tab.

Message
(Y row(s) affected)

Where Y is the number of rows affected by the Merge operation.

Remember, both sqlcmd and DbSchema require you to have the necessary permissions on the source and target tables to execute Merge statements.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

The Merge statement in SQL Server is a powerful feature that can simplify and enhance data synchronization tasks between tables. Despite certain limitations, its benefits are significant. We also illustrated how to use the Merge statement with sqlcmd and DbSchema. With these tools, you can easily manage your SQL Server databases and leverage the power of Merge for your data manipulation tasks.

References

1. Microsoft Docs: MERGE (Transact-SQL)
2. DbSchema Documentation
3. Microsoft Docs: sqlcmd utility