How to use Merge Statement in the SQL Server

In this post, we will discuss the MERGE statement in SQL Server. So, what is the use of the MERGE statement? The MERGE statement, introduced in SQL Server 2008, allows us to perform insert, update, and delete operations in a single statement.

We can say that we don’t have to write multiple SQL scripts to perform CRUD (Create, Read, Update, Delete) operations with the help of the MERGE statement.

We require two tables: a source table and a target table. The source table contains the changes that need to be applied to the target table, while the target table is the table that requires changes. It is the table upon which we will be performing insert, update, and delete operations.

Using the MERGE statement, we join the target table and source table by using a common column between both tables. Based on how the rows match up, we can then perform insert, update, or delete operations on the target table.

Let’s look at an example now.

Here, we have the TblCustomerSource and TblCustomerTarget tables. TblCustomerTarget is the table on which we will be performing insert, update, and delete operations all in one statement.

We will be using the ID column in both the tables to join them.Now, when the rows match between the tables, we want to perform an update on the target table.

If we examine the data that we have at the moment in both tables, we have a record with ID equals 2.So, those rows match, and we want to do an update, meaning we want to update 'Dr. Jacob' to 'Jacob'. So, when rows match, we want to perform an update."

When Rows do not match.

That is when the rows are present in the TblCustomerSource but not in the target table. We want to insert all such rows into the target table at the moment.

Mike’s record with ID=1 is present only in the source table. It’s not present in the target table. So, we want to insert that record into the target table.

And finally, when the rows are present only in the target table, but not in the source table.

we want to delete all such rows from the target table at the moment. Within the target table, we have the record for Johnson with Id=3. It’s not present in the source table, so we want to delete it from the target table.

So after we execute the merge statement, this is how we want the output to be

Notice Dr. Jacob Is updated to Jacob.

Mike’s record, which is present only in the source table but not in the target table, is now inserted into the target table.

The record for Johnson, which is present only in the target table but not in the source table, is deleted from the target table. Here is the syntax to do it.

MERGE [dbo].[TblCustomerTarget] AS T
USING [dbo].[TblCustomerSource] AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.CustomerName = S.CustomerName
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, CustomerName) VALUES(S.ID, S.CustomerName)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

So, in this MERGE statement, we specify the target table as [dbo].[TblCustomerTarget] with the alias 'T', and then we specify the source table as [dbo].[TblCustomerSource] with the alias 'S'. We define a join condition between them.

When the rows match based on that condition, we want to perform an update on the target table. So, we specify 'UPDATE SET T.CustomerName = S.CustomerName'. This means that when the rows match, we perform an update.

When not matched by Target, it means when the rows are present in the source table but not in the target table, then we want to do an insert. So, we include an insert statement.

INSERT (ID, CustomerName) VALUES(S.ID, S.CustomerName)

when not matched by the source. This means when the rows are present in the target table but not in the source table, then we want to perform a delete, so pretty straightforward syntax there.

DELETE;

Merge statement must end with a ‘,’,

Table Script

/****** Object: Table [dbo].[TblCustomerSource] Script Date: 12/07/2020 6:00:18 PM ******/
GO
CREATE TABLE [dbo].[TblCustomerSource](
[Id] [int] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[Gender] [nvarchar](50) NULL,
CONSTRAINT [PK_TblCustomerSource] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblCustomerTarget] Script Date: 12/07/2020 6:00:18 PM ******/
GO
CREATE TABLE [dbo].[TblCustomerTarget](
[Id] [int] NOT NULL,
[CustomerName] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
CONSTRAINT [PK_TblCustomerTarget] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (1, N'Mike', N'Male')
GO
INSERT [dbo].[TblCustomerSource] ([Id], [CustomerName], [Gender]) VALUES (2, N'Jacob', N'Male')
GO
INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (2, N'Dr. Jacob', N'Male')
GO
INSERT [dbo].[TblCustomerTarget] ([Id], [CustomerName], [Gender]) VALUES (3, N'Johnson', N'Male')
GO