How to use merge statement in the SQL server

In this post will discuss the merge statement in the SQL server. So what is the use of the Merge statement? Merge statement, introduced in SQL 2008, allows us to perform insert updates and delete in one statement.

we can say that we don’t have to write multiple SQL scripts for performing, Crud operation or insert update, and delete with the help of Merge statement.

We have required two tables a source table and a target table. The source table is the table that contains the changes that need to be applied to the target table

The target table is the table that requires changes. It is the table upon which we will be performing insert updates and delete.

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

Let’s look at an example now.

Here we got TblCustomerSource and TblCustomerTarget tables. TblCustomerTarget is the table on which we will be performing insert update and delete 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 look at the data that we have at the moment in both the 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 Johnson record 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. It’s now inserted into the target table.

Johnson record, which is present only in the target table but not in the source table, is deleted from the target table, and 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 merge with specifying the target table. So in our case, the target table is [dbo].[TblCustomerTarget] ‘As’ and we give it an alias T and then using the source table.

So in our case, the source table is [dbo].[TblCustomerSource] And we give it alias ‘S’ on specified a join condition.

when the rows match based on that condition, then we want to perform an update on the target table. So we are saying UPDATE SET T.CustomerName = S.CustomerName. when the rows match, perform an update.

When not matched by Target. This 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 have 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

The post How to use merge statement in the SQL server appeared first on Software Development | Programming Tutorials.