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
Nice article…I have one doubt…
Do we need to select StudentName in the Source Table like
“SELECT StudentID,StudentName FROM StudentDetails”, which is never used in the Join?
It is required. See the third line of the MERGE statement
Dear Sir I just checked and found that studentname the Merge is working fine…
Dear Sir I just checked and found that without the studentname the Merge is working fine…
can we transfer different combo box value into different tables at the database by using one one sql insert query
Give us more informations to help you
What if we want a conditional merge or insert
Eg: if(condition = true)
(
insert into table1 values(select * from tableE) where ..
)
else
(
)
how does such things work, can u help me.
Thanks
Nice Article & Nice Feature of SQL Server 2008
Thanx
Thanks
Hi Pinal Dave,
Thanks for most of the very useful articles you write..Just something that still puzzles me and you might have the answer.
To be it seems 2008 MERGE statement and CDC perform the same function during the ETL process that is best used in SCD situations.
What i do see is CDC tracking and capturing data into a Different Table..
My question is do they perform different functions.
Thanks
How to merge 2 tables … eg : Table1 and Table2 , these two tables should be merged into Table3 .. means creating this new table3 contaning the data of table1 and table2
You cannot create a table using MERGE. We can move the data from tables into one table.
MERGE TABLE3 as T3
USING ( select T1.*,T2.* from T1,T2 where join condition of T1 and T2) T4
ON (Join condition of T3 and Source tables)
WHEN NOT MATCHED THEN
INSERT (Column List)
VALUES( );
You can use the union all to get all the rows of both tables and make third table.
Thanks
Manish
thenqq Imran :)
one more doubt ,is it possbile to combine the 2 tables into a new table .. The join keyword is only selecting the information.
SELECT * INTO new_table FROM
(
your_join_statement
) as t
Can you cancel or stop a MERGE statement ? What if I need to Merge 2 Huge tables and then for some reason, I would like to cancel the curent operation. Is this possible ? Can I Rollback ? Thanks
Hi Pinal Dave,
really great information!!
please tell me how to merge data into two table
like newtable has data for table1 and table2
table1 and table2 has matching columns
so how i’ll fetch table1’s new indent value to insert record for it into table2
—
thanks in advance
in sql server 2005 how we can achieve merge feature?
You cant use Merge in SQL 2005
–Create a target table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
–Insert records into target table
INSERT INTO Products
VALUES
(1, ‘Tea’, 10.00),
(2, ‘Coffee’, 20.00),
(3, ‘Muffin’, 30.00),
(4, ‘Biscuit’, 40.00)
GO
–Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
–Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, ‘Tea’, 10.00),
(2, ‘Coffee’, 25.00),
(3, ‘Muffin’, 35.00),
(5, ‘Pizza’, 60.00)
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO
Next I will use the MERGE SQL command to synchronize the target table with the refreshed data coming from the source table.
MERGE SQL statement – Part 2
–Synchronize the target table with
–refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
–When records are matched, update
–the records if there is any change
WHEN MATCHED AND TARGET.ProductName SOURCE.ProductName
OR TARGET.Rate SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
–When no records are matched, insert
–the incoming records from source
–table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
–When there is a row that exists in target table and
–same record does not exist in source table
–then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
–$action specifies a column of type nvarchar(10)
–in the OUTPUT clause that returns one of three
–values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’,
–according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
this code works..try..
[email removed]
Hi Rakshit,
The above code doesn’t work.
Because in NOT MATCHED condition we cannot perform the DELETE operation.I tried the same.
If am wrong correct me.
Thanks
Hi pinal,
I want to update or insert into more than one tables so can we use multiple merge statement in one StoredProcedure itself
Hello Divya,
There is no maximum number limitation on merge statements in a stored procedure. Are you facing any issue in writing multiple merge statements in a stored procedure?
Regards,
Pinal Dave
An action of type ‘WHEN MATCHED’ cannot appear more than once in a ‘UPDATE’ clause of a MERGE statement.
i m getting this error when i m trying to use matched condition for update more than once so how i can handle this issue…
please guide me soon as possible…
i am really in urgency to complete this task
Nice job!
Hello,
My need is if the rows are not macthing I need to insert them but one at a time as I have to get the max(id) + 1 for each insert to be able to populate it.
How can I insert one statement at a time just after I get select @traceid = max(id)+1
insert into abc (@traceid, ‘test’)
Where u able to fix it .. I also face the same issue.
Hi, Dave
I Need to act this Merge as REPLACE behavior in MySQL
as far i use like this query to implement it
MERGE INTO StudentDetails as a
USING (SELECT ‘4’ as StudentId) as b
ON a.StudentId = b.StudentId
WHEN MATCHED THEN
UPDATE SET StudentName = StudentName+’ UPDATED’
WHEN NOT MATCHED THEN
INSERT (StudentId, StudentName) VALUES (‘4’, ‘Martin Inserted’);
previously on SQL 2005 I Use IF EXISTS and DELETE then insert
What about the old fashioned left join for upsert?
UPDATE tbl_tempManifest AS tm
LEFT JOIN tbl_Manifest AS m ON tm.tracking=m.tracking
SET m.name = tm.name,
m.store = tm.store,
m.orderNum = tm.orderNum,
m.tracking = tm.tracking;
This update’s or inserts, would I benefit from using MERGE in this situation?
This is the syntax for ACCESS. For SQL Server SET should come first before joins
Sothing strange, i have sql 2008
when matched => update is ok
WHEN NOT MATCHED => not run at all
—————————————-
merge into [import].[CityImport] target
using
( SELECT CityId ,CityName FROM [import].[CityImport] WHERE CityId=@CityId ) source
on
(
target.CityId=source.CityId
)
when matched then
UPDATE
SET Target.[CityName] = @CityName
WHEN NOT MATCHED THEN
INSERT
(CityId ,CityName )
VALUES
(1333331,
‘111’) ;
END
How we can do same type of merging in SQL server 2005?
for eg:
GroupId player
————————–
1 P-1
2 P-2
3 P-3
1 P-11
2 P-21
1 P-12
Result should be like this:
GroupId player
————————–
1 P-1,P-11,P-12
2 P-2,P-21
3 P-3
Please provide the solution as my requirements
Thanks in advance
SELECT DISTINCT t1.GroupId,
STUFF((SELECT DISTINCT TOP 100 PERCENT ‘,’ + t2.player FROM table AS t2 WHERE t2.GroupId = t1.GroupId ORDER BY ‘,’ + t2.player FOR XML PATH(”)), 1, 1, ”) AS player
FROM table AS t1
ORDER BY t1.GroupId
Thanks Madhivanan,
Your query is working as my expectation.
SQL STUFF is Very nice stuff. I do have question about your query. Because I want to understand your query. why did you use DISTINCT in sub query? This query result is same without DISTINCT in sub query.
I mentioned this place DISTINCT not main query DISITINCT :-
SELECT DISTINCT TOP 100 PERCENT ‘,’ + t2.player FROM table AS t2 WHERE t2.GroupId = t1.GroupId ORDER BY ‘,’ + t2.player FOR XML PATH(”)
Please reply if any specific reason
It is becasue it player is repeated for same groupdid, it wuill be duplicated