Merge Statement in SQL Server
In this post I will explain all about Merge Statement in SQL Server. So let’s start.
Merge Statement in SQL Server
Microsoft introduced the Merge statement into SQL Server 2008 to perform the INSERT, UPDATE and DELETE statements into a single statement. The SQL Merge Statement is used to modify the data present in a target table based on data in the source table, which means Merge Statement in SQL Server joins the required table with the target table or view and then performs the operation needed against them.
In another means we can understand it – Merge Statement in SQL Server is the combination of three INSERT, UPDATE and DELETE statements and with the help of Merge statement we can perform all these three operations in our main target table when the source table is provided. Let’s clarify all our doubts about the Merge statement with the help of an example. Consider we have a target Dup in which we have to use these operations and also we have a source table EMP which contains the latest details and according to the data provided in the DUP table we want to make changes in the main EMP table.
Graphical Representation –
Syntax: The basic syntax of the Merge statement in SQL Server is as given below –
MERGE [Destination Table Name] AS DUP
USING [Source Table Name] AS EMP
ON DUP.[Common Column]=EMP.[Common Column]
WHEN MATCHED THEN INSERT | UPDATE | DELETE
WHEN NOT MATCHED THEN INSERT | UPDATE | DELETE
From the above syntax, it will check expression after the matched keyword and operate.
Basic rules to use Merge Statement
- In Merge Statement we have to specify the table name or View name on which we want to perform the operation like INSERT, UPDATE, and DELETE statement.
- We have to specify the data source that we want to join in the USING clause.
- We have to specify the common column names that we want to use for joining both the tables using the ON clause.
- We can use or perform these three statements INSERT, UPDATE and DELETE operation based on the result we get from the WHEN clause like “WHEN MATCHED and WHEN NOT MATCHED”.
- We can not update the same variable more than once in the same MATCHED clause.
- We have to use the semicolon(;) to terminate the MERGE statement.
- We can use the @@ROWCOUNT after the MERGE Statement as @@ROWCOUNT will return the number of rows INSERTED, UPDATED or DELETED.
For this SQL Merge statement example demonstration, we can go ahead with two(2) different tables like EMP (Source) table and DUP(Target) table which is not present in my database, but I will do all steps here to clarify you step by step –
CREATE A NEW TABLE EMP(Source Tabel)
Again will create another DUP table as Target table where we will use the MERGE statement.
CREATE A NEW TABLE DUP (Target Table)
Now I will add some values into the Source table (EMP) to show the live examples –
Now i will show you that what values are available in both tables –
We can easily see that the DUP(Target) table is not showing any values yet the EMP(Source) table is showing 3 records which we have inserted previously. Now we will apply MERGE statement into DUP(Target) table –
We can see easily here that all records have been inserted successfully into the Target table(DUP) from the Source table(EMP) which was present in the EMP table but was not into the DUP table.
Let’s see result using SELECT statement –
We can see that the same records are showing into both tables, this is what we expecting by this MERGE statement. We have done a test for an INSERT statement, you can do more tests and check for your best practice but I am leaving it here and will go ahead with next test for an UPDATE statement, let’s see –
In the above screen I have updated on records salary as 12000 instead of 7000 for the employee whose name is Raghawendra and ID is 1. Now we will run the SELECT Statement to see the variance in data between both the tables –
We can see the exact difference in above result record set of both the tables as in EMP(Source) table Raghawendra’s Salary is showing 12000 which we have updated earlier but in DUP(Target) table its still old one as 7000, Now we will run again MERGE Statement along with SELECT statement to see the exact operation done internally. let’s see –
We can see that the same records are showing into both tables, this is what we expecting by this MERGE statement. We have done test for UPDATE statement, you can do more tests and check for your best practice but i am leaving it here and will go ahead with next test for DELETE statement, let’s see –
In the above screen, I have deleted one record which employees ID is 3. Now we will run the SELECT Statement to see the variance in data between both the tables –
We can see the exact expectation in the above result record set of both the tables as in the EMP(Source) table is showing only 2 records as we have deleted one record earlier but in DUP(Target) table it’s still showing 3 records.
Assuming this tutorial post has well-acquainted aspiring to the complete requirement.
Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.
Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.