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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?