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
Thank you!! From beginning until now your articles are my first help!
nice post
I am having error:- incorrect syntax near as please help
Sir,
How to delete from target table if some record not match with source table. example
ALTER PROCEDURE dbo.Sed_TimeTable_Save2
@TimeTable dbo.[Sed_TimeTable_TYPE] Readonly
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
BEGIN
MERGE [dbo].[Sed_TimeTable] AS t
USING @TimeTable tvp ON tvp.[TimeTableID]=[t].[TimeTableID]
AND [tvp].[ClassID] = [tvp].[ClassID]AND [t].[DayID] = [tvp].[DayID] AND [tvp].[SectionID] = [t].[SectionID]
WHEN MATCHED THEN
UPDATE SET [ClassID]=[tvp].[ClassID],[DayID]=[tvp].[DayID],[FID]=[tvp].[FID],[Note]=[tvp].[Note],
[PeriodID]=[tvp].[PeriodID],[SectionID]=[tvp].[SectionID],[StaffID]=[tvp].[StaffID],[SubjectID]=[tvp].[SubjectID]
WHEN NOT MATCHED BY TARGET THEN
INSERT([ClassID],[DayID],[FID],[Note],[PeriodID],[SectionID],[StaffID],[SubjectID])
VALUES([tvp].[ClassID],[tvp].[DayID],[tvp].[FID],[tvp].[Note],[tvp].[PeriodID],[tvp].[SectionID],[tvp].[StaffID],[tvp].[SubjectID])
WHEN NOT MATCHED BY SOURCE AND
DELETE;
END
IF @@ERROR >0
ROLLBACK
ELSE
COMMIT
END
GO
Hi Pinal Dave,
i have two tables:
Table A Table B
ID Name ID Name
1 P 4 T
2 Q 5 S
3 R
Please give me a query using joins for this o/p,
OutPut:
1 P
your query is showing the following error when i run
Incorrect syntax near the keyword ‘AS’
Hi Pinal,
i ran your query .your query throws the following error
Incorrect syntax near the keyword ‘AS’
Hi Pinal ,
Please correct your query .It is throwing error.
Thanks
hi,
i have 2 database with same schema that were in 2 different location,
now i want to merge the data of both the database but the issue is i cannot use a direct backup as the data of the other location will be erased.
second issue is due to same schema both have PK as there ID and in both the database and so with ID 1 both the table have different value how can I do that the my main database remain the same and the new script/backup get updated/attached to it with its values to my database.
thanks,
HI
Help me here!
I have the list of both old cost centres and new coat centres and i want to change old cost centres to new cost centres information in SQL server management studio r2.
So i want to come up with a SQL script to change old cost centres to new cost centres.
good job. keep going. Thanks!
I want to use the Merge Statement but I need to give two different search condition since it has to fulfill those two search conditions. Can I put OR and do that or is there any better way of doing it?
Thank you.
Hi Pinal,
I have table like this:
ApprovalSeq RequestNumber PositionID EmployeeName
======================================================================
1 REQ001 IT Manager JOhan
2 REQ001 IT Manager Hendrik
3 REQ001 IT Staff Roni
Since ApprovalSeq 1 & 2 has same PositionID, therefore it supposed to be like this:
ApprovalSeq RequestNumber PositionID EmployeeName
======================================================================
1 REQ001 IT Manager JOhan; Hendrik
2 REQ001 IT Staff Roni
Please advise
Thanks.
very well explained … thanks a ton
i need program for this:
Create an Package to validate and load data based on the condition given below. Please let me know if you have any doubt
select * from eu_equipment_stg2
–Validate against eu_equipment.user_key=eu_equipment_stg2.user_key
— IF Record Exists then
— Update the status as Error and Message as ‘User Key Exist in Base Table’
— IF Record Not Exists then
— Update the status as Success
— Finally Move all the success records to Interface table(EU_EQUIPMENT_INTERFACE) and update the status as Interfaced.
Dear Pinal,
Delete and insert is happening(Found with the help of CDC) while updating non key values with merge statement, my requirement is to go for only update. can you please help me out on this.
Regards,
Nageswar
You can use “output” clause.
Hi Pinal:
I came across this good article of yours. Have you done any performance/speed comparisons between some various methods of doing this? For instance a good blog article which benchmarks various ways of accomplishing an “insert where not exists” task can be found here:
They use a few techniques: “Insert Where Not Exists”, “Merge” statement, “Insert Except”, and your typical “left join” to see which way is the fastest to accomplish this task. It’s a good read for those who are looking for speed!
How to do that if data come up from XML file?
Very nice
Hi how this will work on business layer like this
public List GetMemberList()
{
try
{
return (new DALMember()).GetMemberList();
}
catch (Exception ex)
{
throw ex;
}
}
public BOMember GetMemberById(int memberId)
{
try
{
return (new DALMember()).GetMemberById(memberId);
}
catch (Exception ex)
{
throw ex;
}
}
========================================================================================
public class DALMember
{
SqlHelper oSqlHelper;
public List GetMemberList()
{
List returnList = new List();
try
{
List parameterList = new List();
oSqlHelper = new SqlHelper();
DataSet ds = oSqlHelper.ExecuteDataSet(“GetMemberList”, parameterList);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow drItem in ds.Tables[0].Rows)
{
returnList.Add(new BOMember()
{
MemberId = Convert.ToInt32(drItem[“MemberId”]),
FirstName = Convert.ToString(drItem[“FirstName”]),
LastName = Convert.ToString(drItem[“LastName”])
});
}
}
oSqlHelper = null;
}
catch (Exception ex)
{
throw ex;
}
return returnList;
}
public BOMember GetMemberById(int memberId)
{
BOMember returnData = new BOMember();
try
{
List parameterList = new List();
parameterList.Add(SetSqlParameter.GetSqlParameter(“@MemberId”, SqlDbType.Int, memberId));
oSqlHelper = new SqlHelper();
DataSet ds = oSqlHelper.ExecuteDataSet(“GetMemberById”, parameterList);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow drItem in ds.Tables[0].Rows)
{
returnData.MemberId = Convert.ToInt32(drItem[“MemberId”]);
returnData.FirstName = Convert.ToString(drItem[“FirstName”]);
returnData.LastName = Convert.ToString(drItem[“LastName”]);
}
}
oSqlHelper = null;
}
catch (Exception ex)
{
throw ex;
}
return returnData;
}
}
========================================================================================