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
This is perfect this helped me in understanding what a merge statement is
Can we have multiple source tables?
ex:
merge targettable t
using (select s1.col1, s2.col2 from source1 s1 join source2 s2
on s1.col3 = s2.col3 ) sourcetable s3
when matched
then update set t.col1 = s3.col1,
t.col2 = s3.col2 ;
instead above query if write like
merge targettable t
using
source1 s1
join source2 s2
on s1.col3 = s2.col3
when matched
then update set t.col1 = s1.col1,
t.col2 = s2.col2 ;
Hi,
I have 2 tables which has only one column and contains mobile numbers. I want to combine both the tables and want to make 1 table and want to remove duplicate entries after that. Pls suggest the easiest way to perform the same.I am using SSMS 2008 version.
Regards,
Amit.
Nice Article …the understanding has been so clear that I dont need to go anywhere else..
How can i implement multiple Update In Merge Statement
I was excited to use this feature and replace this wiht my legacy insert/update statment i was writting for SSIS package to sync my data however the exececution plan show that it is not performance effective and it was using 87% of the time comparet to my normal insert/update. has any one else tested the performance of the query.
hi,
anyone tell me how can I update target table using Merge if conditional field has more then one record
This article is more useful for me.your example queries are very simple and more useful for all :)
getting below error even i am updating single column that too unique records
Msg 8672, Level 16, State 1, Procedure R_verificationData_BatchJobs, Line 5
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Sir table1 is my base table i want to reverse your query i am Trying
MERGE table1 AS m
USING (SELECT ServiceAreaCode,PhoneNumbers,Preferences,Opstype,PhoneType FROM table2) AS m1
ON m1.PhoneNumbers = m.PhoneNumbers
WHEN MATCHED AND m.Opstype = ‘”a”‘ THEN DELETE;
but it give errore
Msg 547, Level 16, State 0, Line 1
The MERGE statement conflicted with the REFERENCE constraint “FK__manoj2__PhoneNum__1273C1CD”. The conflict occurred in database “test”, table “dbo.manoj1”, column ‘PhoneNumbers’.
The statement has been terminated.
Please Help me
Nice Article
whenevr i try merge, it generate error near ‘as’ keyword
Hi sir,
Delete Duplicates And Resolve Foreign Key References using merge?
If possible for set multiple source tables or target tables?please send examples..
hi Pinal,
Is it possible to store the output of the MERGE statement in a history table to refer at a later stage… by outpput i mean the changes that occured as part of the merge.
One thing that is documented very well is that subselects are NOT allowed in the INSERT or UPDATE statements. But they are allowed in the WHEN (NOT) MATCHED clause.
I have found that instead of putting the subselect in the INSERT or UPDATE to place it as a join in the Source table for later use.
— (For example this will NOT work): —>
MERGE users AS Target
USING (
SELECT id, name
FROM locations
) AS Source
ON (Target.id = Source.id)
WHEN NOT MATCHED BY Target
THEN INSERT(name, parent_id, status)
VALUES ( name, (SELECT id FROM users WHERE pa_id = Source.id), 1 )
WHEN NOT MATCHED BY Source AND Target.id IN (SELECT id FROM users
WHERE pa_id IN (1,2))
THEN UPDATE SET status = 0 –deactivate
WHEN MATCHED
THEN UPDATE SET status = 1, pa_id = (SELECT id FROM users WHERE pa_id = Source.id) –reactivate and update parent id.
;
———————————————————–
— (Instead it needs to look more like this): —>
MERGE users AS Target
USING (
SELECT u.id, u.name, l.pa_id
FROM locations l LEFT OUTER JOIN users u ON u.id = pa.id
) AS Source
ON (Target.id = Source.id)
WHEN NOT MATCHED BY Target
THEN INSERT(name, parent_id, status)
VALUES ( name, pa_id, 1 )
WHEN NOT MATCHED BY Source AND Target.id IN (SELECT id FROM users
WHERE pa_id IN (1,2))
THEN UPDATE SET status = 0 –deactivate
WHEN MATCHED
THEN UPDATE SET status = 1, pa_id = pa_id –reactivate and update parent id.
;
Hi Pinal Dave
I am working on Merge statement. Now I have a scenerio where I need to insert a record “when not matched” and also “when matched and Sourcecolumn1!=Targetcolumn1”.
Could you please help me in this rregards?
Thanks
Swetha
INSERT INTO Products
VALUES(1,’Tea’,10.00),(2,’Coffee’,20.00),(3,’Muffin’,30.00),(4,’Biscuit’,40.00)
error: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
what the hell is this error
Are you using version from 2008?
HI friend,
Sorry but as i checked we can do this in sql server 2008 R2 as well as 2008,so no matter whether the server is 2008 ,2008 R2 or 2012
i think you have to check again.
Hi Rakshith,
Copy the below code and try.
INSERT INTO Products
VALUES
(1,’Tea’,10.00),
(2,’Coffee’,20.00),
(3,’Muffin’,30.00),
(4,’Biscuit’,40.00)
Rakshith,
I copied your code and tried parsing ino SQL query. But there was an error as mentioned by you.
I then replaced the single ” ‘ ” from the query by typing the same from key board. It worked fine.
When I posted the comment and copied my code from this blog ( from my previous comment) and tried parsing . I got the same error.
So, If you are copying and pasting the code, make sure to replace single apostropes by typing from the key board.
Best Regards,
Swetha
Hi Pinal
is it possible to use Merge statement in SSIS package Dataflow i mean, in
OLE DB command transformation