MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.
Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,…n ] ) ]
;
Example:
Let’s create Student Details and StudentTotalMarks and inserted some records.
Student Details:
USE AdventureWorks GO CREATE TABLE StudentDetails ( StudentID INTEGER PRIMARY KEY, StudentName VARCHAR(15) ) GO INSERT INTO StudentDetails VALUES(1,'SMITH') INSERT INTO StudentDetails VALUES(2,'ALLEN') INSERT INTO StudentDetails VALUES(3,'JONES') INSERT INTO StudentDetails VALUES(4,'MARTIN') INSERT INTO StudentDetails VALUES(5,'JAMES') GO
StudentTotalMarks:
CREATE TABLE StudentTotalMarks ( StudentID INTEGER REFERENCES StudentDetails, StudentMarks INTEGER ) GO INSERT INTO StudentTotalMarks VALUES(1,230) INSERT INTO StudentTotalMarks VALUES(2,255) INSERT INTO StudentTotalMarks VALUES(3,200) GO
In our example we will consider three main conditions while we merge this two tables.
- Delete the records whose marks are more than 250.
- Update marks and add 25 to each as internals if records exist.
- Insert the records if record does not exists.
Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.
MERGE StudentTotalMarks AS stm USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd ON stm.StudentID = sd.StudentID WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25 WHEN NOT MATCHED THEN INSERT(StudentID,StudentMarks) VALUES(sd.StudentID,25); GO
There are two very important points to remember while using MERGE statement.
- Semicolon is mandatory after the merge statement.
- When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.
After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.
AS we can see there are 5 rows updated. StudentID 2 is deleted as it is more than 250, 25 marks have been added to all records that exists i.e StudentID 1,3 and the records that did not exists i.e. 4 and 5 are now inserted in StudentTotalMarks .
MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.
Reference : Pinal Dave (https://blog.sqlauthority.com)
127 Comments. Leave new
Thanks Madhivanan,
Your query is working as my expectation. Can you provide some link which is new in sql server 2005?
Refer this to know what the new features available in version 2005 are
https://www.microsoft.com/en-us/sql-server/sql-server-2016
I’m attempting to write a merge statement and have the original merge table be dynamic so it can run for multiple tables using a for loop. However, it seems I cannot have the table name for the merge be a variable. . .is this not possible? Example:
MERGE @DestinationTableName AS Test
Using( etc. . .
Nice finding to me.Your articles are straightforward and easy to understand. Thanks for your nice contribution.
I have Employee and Emplyee History. If any employee is inserted then I have to create a new record in History with the Inserted Id. I think we cannot write in When Not Matched Clause because it allows only one Insert statement
Hi, Firstly thanks for the article seems that it’s going to save heaps of times/code fro me, but i have similar issue as Rajkumar does, One of target’s field is a foreign key from another table which I have to insert into that one first, Any idea , tips or tricks or here I missing something?
Nice article.This is easy to understand.
Thanks.
Thanks
Hi Sir,
How do we get the record count of the affected transactions on the Merge Insert, Update, & Delete Statements?
Thanks
MERGE StudentTotalMarks AS stm USING ( SELECT StudentID,
StudentName
FROM StudentDetails
) AS sd
ON stm.StudentID = sd.StudentID WHEN MATCHED AND stm.StudentMarks > 250
THEN DELETE WHEN MATCHED THEN UPDATE
SET stm.StudentMarks = stm.StudentMarks + 25 WHEN NOT MATCHED THEN
INSERT ( StudentID, StudentMarks )
VALUES
( sd.StudentID, 25 ) ;
This Code Generate Error…
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘AS’.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘AS’.
Hi Pinal, this is very nice and helpful article..
I have Implemented merge for pulling data fro my source system where i pull around 58000 records.
First time the MERGE Statement took around 1 hour 15 mins.
I have a condition in my MERGE statement after the ON condtions checking lastmodified_date_onserver > max(lastmodifieddate at my server)
Ideally i was expecting this to run on a subset of data dn give me faster response. It doesn’t. it is still taking same one hour time.
Any suggestion on where to place the condtion and what can i do to make the query more effective.
thanks,
Aditya
Thnx for such a nice article.
Hi Pinal,
nice article……..
thks a lot…..
Hi Pinal,
Thanks a lot for such a nice article.
Hi Pinal.
Interesting article. Here you are ‘the hero’…
But I expose the next situation; I need merge some tables (for example table1 and table2) for two databases (BD1 as central and BD2 as extension) that have references between them, table1 is referenced for table2. If execute merge for table1, the data that exists in the table2 of BD2, in the reference value, not will be the same value after the merge. ¿how I do to merge the data in table2, if the reference value in table1 has changed and, I need do the insert with this value in the merge for table 2?
Example.
Table1: Customer
IDCustomer = 1,
NameCustome = JOHN.
Table2: CustomerPhone
IDCustomerPhone = 1,
IdCustomer = 1,
NumberPhone = 1234567890
IDCustomerPhone = 2,
IdCustomer = 1,
NumberPhone = 0987654321
For example the IDCustomer has change in the merge to 5, and this value is necesary for the merge in the table2. ¿How I do for make this, without problems?
Thanks in advance,
Very nice article pinal.
Very very good article Pinal.
I always read your articles.
Great Article
hi nice article pinal, easy to understand ,clarly, what a man great
CaN WE MERGE 2 DATABASES I?
Nice article !! thanks