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
i used table varible in a SP to tempararly store the data , to compare using Merge statement , is there any alternate solution for this
You can alos use a temporary table too
Hi Nagendra,
Are source and destination database are same?
Swetha
Does merge operator works with only inner joins or we can use it with left , right and self join….please advice.
Hi Asha,
Use merge statement in stored procedure and execute the stored procedure from .net.
Swetha
Your blog is very helpfull,
One thing should include into article that we can not use alias name with column names in Insert statement :
WHEN NOT MATCHED THEN
INSERT(stm.StudentID,stm.StudentMarks)
VALUES(sd.StudentID,25);
and if we will do this we will get following error:
“The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.”
Hi,
I have implemented SSIS package where i have used CDC feature to do insert/update/delete in table. can you please help me if we will go with Merge feature then will it make any difference in terms of performance.
Hi Pinal,
I want to update a table which is not the source/target table in “when not matched” section using update query. is that possible?
For example –
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN MATCHED
THEN DELETE
very thanks
My code:
merge into #TEMP_table as Target
using (
select scol1, scol2, scol3, scol4 from test
) as Source
on Target.tcol1=Source.scol1 and Target.tcol2=source.tcol2
when matched then blah blah….
but I keep getting that it cannot insert a row that already exists (my temp tables pk violated).
When I search my source table, group by count there are none that are listed more than one time that are unique accross scol1 and scol2.
select scol1, scol2, count(*) from test group by scol1, scol2 having count(*)>1 no rows returned
Am I doing the on correctly to match against both columns?
This MERGE really awesome!
Need to know one thing. I am using this to update data from excel files each month. Now if the user was in the database before but I get a new excel and he is not in there, I want to update the db record for him to inactive. Is there a way to do this?
@Derek, I am not PinalDave, but I didnt want to leave you hanging. There is an “when not matched by” option. So:
merge into TargetTable as Target
using (
select scol1, scol2, scol3, scol4 from SourceTable
) as Source
on Target.tcol1=Source.scol1
when matched then
blah blah….
when not matched by Source then
–this one is not found by source so do something
when not matched by Target then
–this one is not found by Target so do something
Remeber to test all on a QA or Test system before implementing in a prod envoronment.
@Terrence Thanks! Appreciate it!
new concept explain by you and it is very useful to beginner
When I am trying to use under given syntax it is showing an error
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);
************************************
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘AS’.
hi.I have only one table and column name is ID, Name ,Section, Amount i want to merge and sum the amount .update in one row and remove duplicate row..if in same there is same name and section..thanks in advance
if there is same name and section *
Hello,
I have a small problem with the merge statement, it involves updating when no changes are present.
For example, Table A (TARGET) has 2 records, Table B (SOURCE) has 2 records. Table A has an auditing trigger set, anytime a record is updated, an audit table record is created. The tables have many fields and on update, there are many tables that are joined and the values are derived from other tables.
The records in both tables are identical, so when I run the merge command, the ‘WHEN MATCHED THEN’ clause is hit, the records in the TARGET table are updated, and two new records are created in the audit table.
The knee jerk reaction is to modify the trigger, but I would like to see if I can do this within the merge command.
Thank you for your time,
Edward Meshuris
hai, i have a setup where i need to store some data in 2 databases of SQL 2008 for redundant purpose. the SQL server is running in 2 different machines. both the machines collect the same data from same source (other server located in the same network) and store them. the problem i am facing is, if one server is switched OFF for say 1 hour, the server that was OFF, will not have the data of that period. when the server is back in service, the table will not be the same as in the database of second server.
i want some procedure where we can check whether the 2 tables are different and if yes, merge the data such that both becomes same. structure of database, tables are exactly identical.
any help in this is appreciated.
Hi Pinal,
i have a small prblem with merge condition, i am getting error as “Incorrcet syntax near the keyword as”. ,my query is
MERGE EventHistory AS EH
USING (select distinct ParcelId, accountno,ImagedDateTime, CreatedDateTime,DelDateTime
from parcel
where AccountNo = ‘072049’
AND CreatedDateTime > getdate() -1000
AND (ImagedDateTime is Not Null or DeldateTime is Not Null))AS PT
ON PT.ParcelId = EH.ParcelId
WHEN MATCHED THEN
UPDATE SET EH.ImagedDateTime = PT.ImagedDateTime , EH.DelDateTime = PT.DelDateTime
WHEN NOT MATCHED THEN
INSERT(ParcelId,DelDateTime,CODSentDateTime,ImagedDateTime,PODSentDateTime,LastSentDateTime)
VALUES(PT.ParcelId,PT.DelDateTime,NULL,PT.ImagedDateTime,NULL,NULL)
how to resolve it. please help me.
hi pinal i am new in sql server
but i want to ask you that this merge is just like our user defined function, or procedure
in which we can develop our own logic
Thank you for all your help! New to SQL and I end up using your articles from Google searches more then anyone else’s. Very easy to follow!
Thanks again and keep up the good work!