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 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

2.New_User Table

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:

Explanation:

• 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

Explanation:

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.

sharmaankit

Follow

Improve

Article Tags :

• SQL Server
• Geeks Premier League
• Databases
• Geeks Premier League 2023