Open In App

SQL Server MERGE Statement

Last Updated : 01 Jan, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

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

SNO

user_id

username

email

1

1

JohnDoe

john.doe@email.com

2

2

JaneSmith

jane.smith@email.com

3

3

BobJohnson

bob.johnson@email.com

2.New_User Table

SNO

user_id

username

email

1

1

JohnDoe

john.doe@newemail.com

2

3

BobJohnson

bob.johnson@newemail.com

3

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:

Example1

Example1

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

Example2

Example 2

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.



Similar Reads

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 applications to large, enterprise-level databases. It pr
6 min read
What is the CASE statement in SQL Server with or condition?
In SQL Server, the CASE statement cannot directly support the use of logical operators like OR with its structure. Instead of CASE it operates based on the evaluation of multiple conditions using the WHEN keyword followed by specific conditions. In this article, we will learn about the OR is not supported with CASE Statement in SQL Server with a de
4 min read
What is Nested Select Statement in SQL Server
SQL Server is a powerful relational database management system. Sql Server is very good with its robustness and scalability. SQL Server operates as a client-server structure, imparting centralized management and scalability for huge-scale applications and enterprise-stage solutions. It offers advanced features which include excessive availability,
3 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 help of various examples and so on. SQL Server INSERT
4 min read
Select Statement in MS SQL Server
The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to manipulate and analyze the data. In this article, We w
4 min read
Dynamic SQL in SQL Server
In SQL Server, at times the SQL Queries need to be dynamic and not static, meaning the complete SQL query may be built dynamically at run time as a string using the user inputs and any specific application logic. This can be done in queries run from back-end applications or inside stored procedures. In this article let us look into the details abou
6 min read
Dynamic SQL and Temporary Tables in SQL Server
In SQL Server, creating and using Temp Tables using dynamic SQL is a good feature when we need to temporarily create tables at run time and delete automatically all within a session. They can be very useful when we need to store temporary data in a structured format and do data manipulation using Data Manipulation Language in SQL. In this article l
6 min read
How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then querying the outcomes. The idea of utilizing SQL
3 min read
SQL Server | Convert Tables in T-SQL into XML
XML (Extensible Markup Language) is a widely-used markup language designed to store and transfer structured data between different systems and platforms. While HTML focuses on the visual representation of data OverviewXML is similar to HTML which is designed to structure and store data for sharing across different systems and platforms.Unlike HTML,
3 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records to the specified table. We can use this statement to add data directly to the table. We use the VALUES keyword along with the INSERT INTO statement. VALUES keyword is accompanied by column names in a specific order in which we want to insert values in them. SELECT statement is used
5 min read
What is Nested Select Statement in PL/SQL?
PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on. Understanding Nested Select Statement in PL/SQLThe n
4 min read
SQL CREATE VIEW Statement
The SQL CREATE VIEW statement is a very powerful feature in RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or many underlying tables. Views help simplify complex
4 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, facilitating various database operations and ana
3 min read
How to Combine LIKE and IN in SQL Statement
Combining 'LIKE' and 'IN' operators in SQL can greatly enhance users' ability to retrieve specific data from a database. By Learning how to use these operators together users can perform more complex queries and efficiently filter data based on multiple criteria, making queries more precise and targeted. This article explores how the LIKE and IN op
4 min read
SQL UPDATE Statement
SQL UPDATE Statement modifies the existing data from the table. UPDATE Statement in SQL The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using the UPDATE statement as per our requirement. In a very simple way, we can say that SQL commands(UPDATE and
3 min read
SQL INSERT INTO Statement
The INSERT INTO statement is a fundamental SQL command used to add new rows to a table in a database. It allows for the insertion of data either by specifying the column names along with the values or by providing values for all columns in the table. In this article, We will learn about SQL INSERT INTO Statement by understanding various examples an
8 min read
SQL DELETE Statement
SQL DELETE is a basic SQL operation used to delete data in a database. SQL DELETE is an important part of database management DELETE can be used to selectively remove records from a database table based on certain conditions. This SQL DELETE operation is important for database size management, data accuracy, and integrity. Syntax:  DELETE FROM tabl
2 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 is commonly used to generate new columns based on certain conditions and provide custom values or control the output of our queries. In this article, We will learn about the CASE Statement in SQL in detail
4 min read
CREATE and DROP INDEX Statement in SQL
The CREATE INDEX statement in SQL is a powerful tool used to enhance the efficiency of data retrieval operations by creating indexes on one or more columns of a table. Indexes are crucial database objects that significantly speed up query performance, especially when dealing with large datasets. In this article, We will learn about How to CREATE an
4 min read
SQL CREATE INDEX Statement
The CREATE INDEX statement in SQL is a powerful tool designed to enhance the performance of data retrieval operations. By creating indexes on tables, databases can quickly locate and retrieve data, significantly speeding up query execution. In this article, We will learn about the SQL CREATE INDEX Statement by understanding various examples in deta
4 min read
Implicit Statement Results in PL/SQL
Implicit statement results in PL/SQL refer to returning the results of a query automatically from a PL/SQL block, procedure, or function without explicitly using cursors or OUT parameters. Implicit Statement Results, introduced in Oracle 12c, helps developers simplify their code by returning query results directly for them to work with other applic
8 min read
SQL Statement to Remove Part of a String
Here we will see SQL statements to remove part of the string. Method 1: Using SUBSTRING() and LEN() functionWe will use this method if we want to remove a part of the string whose position is known to us. 1. SUBSTRING(): This function is used to find a sub-string from the string from the given position. It takes three parameters:   String: It is a
4 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 removing data that is no longer needed or that me
7 min read
PL/SQL UPDATE Statement
The UPDATE statement in the PL/SQL(Procedural Language/ Structural Query Language) is the powerful SQL (Structured Query Language) command used to modify the existing data in the database table. In this article, we will explain the PL/SQL UPDATE Statement, its syntax, and examples in detail. PL/SQL UPDATE StatementThe UPDATE statement in the PL/SQL
7 min read
PL/SQL CASE Statement
PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. The PL/SQL CASE statement is a powerful conditional control structure in Oracle database
4 min read
PL/SQL CREATE TABLE Statement
PL/SQL CREATE TABLE statement is a fundamental aspect of database design and allows users to define the structure of new tables, including columns, data types, and constraints. This statement is crucial in organizing data effectively within a database and providing a blueprint for how data should be structured. In this article, we will explore the
4 min read
PL/SQL Statement level Triggers
Statement-level triggers in Oracle databases execute actions for each transaction, responding to various database events like DML and DDL statements, system events, and user interactions. They act as programmed responses to specific table events, enhancing database management and automation. Stored as named PL/SQL blocks, triggers execute automatic
4 min read
PL/SQL GOTO Statement
PL/SQL also known as Procedural Language/Structured Query Language, PL/SQL is a powerful programming language used in Oracle databases to do interaction with data. One of its features is the GOTO statement, which helps control how a program flows by allowing it to jump to specific statements within the same part of the program. This article will pr
5 min read
How to Use PL/SQL Insert, Update, Delete And Select Statement?
PL/SQL is a powerful extension of SQL specifically designed for Oracle databases. It enables developers to create procedural logic and execute SQL commands within Oracle database environments. In this article, we will explore the usage of fundamental PL/SQL statements such as INSERT, UPDATE, DELETE, and SELECT with the help of various examples that
6 min read
PL/SQL NULL Statement
PL/SQL, the Procedural Language/Structured Query Language, is a database programming language utilized for database management in Oracle. Within this language, the NULL statement is crucial in enhancing code readability and functionality. This article aims to look into the significance of the NULL statement in PL/SQL, exploring its various applicat
5 min read
three90RightbarBannerImg