SQL Server MERGE Statement
Last Updated :
01 Jan, 2024
MERGE is a powerful SQL statement that allows you to perform INSERT/UPDATE/DELETE operations depending on the conditions you specify. It makes it easy to synchronize data between source and destination tables. This makes it efficient to handle data changes in one single statement. MERGE makes it easier to manage complex operations such as data warehousing/data mart/incremental data loading, improving database administration and performance.
In this simple guide, we're breaking down the Merge thing in SQL Server. Whether you're a pro or just starting, this is the key to sorting, updating, and adding data without the headache.
MERGE Statement
Let's break down the concept of the MERGE statement in SQL Server using the analogy of merging two lists.
Imagine you have two lists of people – one is a contact list that the user already has i.e. existing contact list assume it as a target list, and the other is a new list of people you've met recently and assume that list to be the source list. Now, let's update your existing contact list to include the new people you've met, and also do manage changes for people who are already on both lists.
This is where the MERGE clause plays a very important role,
1. Target Table:
- This is your original list, where you already have some contacts. It is the table having the existing contact list of the user.
2. Source Table:
- This is the new list, containing people you've recently met. It is the table having the data of the new people who are new friends of the user.
Let's understand merging with the help of below steps:
Step 1: Specifying Target and Source
MERGE INTO TargetTable AS target
USING SourceTable AS source
- TargetTable: Your existing contact list.
- SourceTable: The new list of people you've met.
Step 2: Defining Matching Criteria
ON target.ContactID = source.ContactID
- This is like saying, "Match people in the target list with people in the source list based on their ContactID."
Step 3: Actions for Matched Rows
WHEN MATCHED THEN
UPDATE SET target.Name = source.Name
- If you find someone who is already in your contact list (MATCHED), update their name in the target list with the new name from the source list.
Step 4: Actions for Unmatched Rows in Target
WHEN NOT MATCHED BY TARGET THEN
INSERT (ContactID, Name) VALUES (source.ContactID, source.Name)
- If you find someone in the source list who is not in your contact list (NOT MATCHED BY TARGET), add them to your contact list.
Step 5: Actions for Unmatched Rows in Source
WHEN NOT MATCHED BY SOURCE THEN
DELETE
- If someone is in your contact list but not in the source list (NOT MATCHED BY SOURCE), consider removing them from your contact list.
Basic Syntax
The MERGE clause has a simple yet powerful structure. Here's a sneak peek at the basic syntax:
MERGE target_table AS target
USING source_table AS source
ON (target.column = source.column)
WHEN MATCHED THEN
UPDATE SET target.column1 = source.column1, target.column2 = source.column2
WHEN NOT MATCHED THEN
INSERT (column1, column2) VALUES (source.column1, source.column2)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Let's break it down:
- target_table: The table you want to update.
- source_table: The table providing the new data.
- ON: The condition to match rows between the target and source tables.
- WHEN MATCHED: What to do when a match is found, usually updating columns.
- WHEN NOT MATCHED: What to do when there is no match, usually inserting new records.
- WHEN NOT MATCHED BY SOURCE: What to do when there are rows in the target table that don't exist in the source, often used for deletions.
SQL Server MERGE Examples
Example 1
Suppose we have two tables with same column datatypes having common data in between both tables. One wants table which has information of both the tables in a single table.
Let's create two tables users and new_users table. Refer to below image and query how I created both tables.
Query
Creating & Inserting
-- Open any database
--Create 'users' table
CREATE TABLE users(
user_id INT PRIMARY KEY,
username VARCHAR(255),
email VARCHAR(255)
);
--Insert dummy data into 'users' table
INSERT INTO users VALUES(
(1, 'JohnDoe' , 'john.doe@email.com'),
(2, 'JaneSmith' , 'jane.smith@email.com'),
(3, 'BobJohnson' , 'bob.johnson@email.com'),
);
--Create 'new_users' table
CREATE TABLE new_users(
user_id INT PRIMARY KEY,
username VARCHAR(255),
email VARCHAR(255)
);
--Insert dummy data into 'new_users' table
INSERT INTO new_users VALUES(
(1, 'JohnDoe' , 'john.doe@newemail.com'),
(3, 'BobJohnson' , 'bob.johnson@newemail.com'),
(4, 'AliceBrown' , 'alice.brown@email.com'),
);
This query will create two tables users and new users in the table. Also, values in both table will be added with above query of the SQL server.
Here is the data of the two tables:
1. User Table
|
1
| JohnDoe
| john.doe@email.com
|
2
| JaneSmith
| jane.smith@email.com
|
3
| BobJohnson
| bob.johnson@email.com
|
2.New_User Table
|
1
| JohnDoe
| john.doe@newemail.com
|
3
| BobJohnson
| bob.johnson@newemail.com
|
4
| AliceBrown
| alice.brown@email.com
|
Performing MERGE of User & New_User
Here is the query for the Merge
Query
-- Perform MERGE operation to sync 'users' with 'new_users'
MERGE users AS target
USING new_users AS source
ON (target.user_id = source.user_id)
WHEN MATCHED THEN
UPDATE SET target.username = source.username, target.email = source.email
WHEN NOT MATCHED THEN
INSERT (user_id, username, email) VALUES (source.user_id, source.username, source.email);
Explanation:
- This query users table as the Target Table
- New users table is source table
- merging on the condition of if target.user_id = source.user_id
- If matched then the updation in the content of the table
- when not matched then inserting the record in the users table
Query:
Here is the query to display the user's table. And let's see the changes int the users table.
-- Output content of users table
SELECT * FROM users;
Output:
Example1Explanation:
- The MERGE operation updated the email of JohnDoe and BobJohnson, inserted AliceBrown but didn't deleted the JaneSmith from the record, and merged the tables effortlessly!
- Here it saves the user time of merging two tables manually selecting common and non common values to create a new table & to write data in new table. Hence the information got merged easily with the help of Merge clause.
Example 2
Let's find the common record of the two tables that is users table and new_users table. So what will do is that if the record is not in both the tables then it will get deleted from the table.
Query
-- Perform MERGE operation to sync 'users' with 'new_users'
MERGE users AS target
USING new_users AS source
ON (target.user_id = source.user_id)
WHEN MATCHED THEN
UPDATE SET target.username = source.username, target.email = source.email
WHEN NOT MATCHED THEN
INSERT (user_id, username, email) VALUES (source.user_id, source.username, source.email)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
EXPLANATION
- Similar declaration of the users and new_users table as the target and source table
- The New statement is when not matched then delete the record from the users table this is the new thing in the example
Output
Example 2Explanation:
This will merge two tables users and new_users table. In the user table, all the records which are common in both the table and the records of the new_user will be copied in the users. Other record in user which are not in common will be deleted.
Conclusion
We have successfully covered the topic of merging two tables with the help of the MERGE clause. It is the best tool for the table data handling in SQL Server. Also, try to practice on your own by creating your own table and dummy data. As more and more practice makes you more fluent in SQL. Also, try to do experiment while writing the query this will help you for better understanding of the topic.
Similar Reads
SQL Server MERGE Statement
MERGE is a powerful SQL statement that allows you to perform INSERT/UPDATE/DELETE operations depending on the conditions you specify. It makes it easy to synchronize data between source and destination tables. This makes it efficient to handle data changes in one single statement. MERGE makes it eas
6 min read
PostgreSQL MERGE Statement
The MERGE statement in PostgreSQL is a powerful data manipulation tool introduced in PostgreSQL 15, enabling conditional INSERT, UPDATE, and DELETE operations in a single command. This feature streamlines data synchronization between tables, making it ideal for tasks such as upserts and handling tab
4 min read
SQL Server SELECT INTO Statement
SQL Server is a relational database management system. SQL Server offers robust security features to protect data integrity and confidentiality. It includes authentication, authorization, encryption, and various mechanisms to secure the database environment. It is designed to scale from small applic
6 min read
Insert Statement in MS SQL Server
The SQL Server INSERT statement is a fundamental command used to add new rows of data to a table. Whether we are inserting specific values, utilizing default values or copying data from another table. In this guide, weâll explore various ways to use the Insert statement in MS SQL Server with the hel
4 min read
SQL CASE Statement
The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing
4 min read
SQL SELECT INTO Statement
The SELECT INTO statement in SQL is a powerful and efficient command that allow users to create a new table and populate it with data from an existing table or query result in a single step. This feature is especially useful for creating backups, extracting specific subsets of data, or preparing new
5 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with
6 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
PL/SQL DELETE Statement
In PL/SQL(Procedural Language/Structured Query Language), the DELETE statement is the powerful command used to remove one or more records from the database table. It is an essential part of database management and enables the users to efficiently manage and maintain the data integrity by selectively
7 min read
SQL INSERT INTO Statement
The INSERT INTO statement is a fundamental SQL command used to add new rows of data into a table in a database. It is one of the most commonly used SQL statements for manipulating data and plays a key role in database management. This article will explore the SQL INSERT INTO statement in detail, sho
8 min read