This blog post is written in response to T-SQL Tuesday hosted by Jorge Segarra. I have been very active using these Merge operations in my development. However, I have found out from my consulting work and friends that these amazing operations are not utilized by them most of the time. Here is my attempt to bring the necessity of using the Merge Operation to surface one more time.
MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted.
One of the most important advantages of MERGE statement is that the entire data are read and processed only once. In earlier versions, three different statements had to be written to process three different activities (INSERT, UPDATE or DELETE); however, by using MERGE statement, all the update activities can be done in one pass of database tables.
I have written about these Merge Operations earlier in my blog post over here SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE. I was asked by one of the readers that how do we know that this operator was doing everything in a single pass and was not calling this Merge Operator multiple times.
Let us run the same example which I have used earlier; I am listing the same here again for convenience.
–Let’s create Student Details and StudentTotalMarks and inserted some records.
USE tempdb 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 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 -- Select from Table SELECT * FROM StudentDetails GO SELECT * FROM StudentTotalMarks GO -- Merge Statement 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 -- Select from Table SELECT * FROM StudentDetails GO SELECT * FROM StudentTotalMarks GO -- Clean up DROP TABLE StudentDetails GO DROP TABLE StudentTotalMarks GO
The Merge Join performs very well and the following result is obtained.
Let us check the execution plan for the merge operator. You can click on following image to enlarge it.
Let us evaluate the execution plan for the Table Merge Operator only.
We can clearly see that the Number of Executions property suggests value 1. Which is quite clear that in a single PASS, the Merge Operation completes the operations of Insert, Update and Delete.
I strongly suggest you all to use this operation, if possible, in your development. I have seen this operation implemented in many data warehousing applications.
Reference: Pinal Dave (https://blog.sqlauthority.com)
52 Comments. Leave new
IS THIS POSSIBLE TO CREATE INSERT, UPDATE AND DELETE IN A SINGLE TRIGGER?
Good alternative to doing the same on SSIS.
I was wondering how does the performance affect for the two Tables being on different Database/Servers?
SQL SELECT Statement
This query is used to select certain columns of certain records from one or more database tables.
SELECT * from emp
selects all the fields of all the records from the table named ’emp’
SELECT empno, ename from emp
selects the fields empno and ename of all the records from the table named ’emp’
SELECT * from emp where empno < 100
selects all those records from the table named 'emp' where the value of the field empno is less than 100
SELECT * from article, author where article.authorId = author.authorId
selects all those records from the tables named 'article' and 'author' that have the same value of the field authorId
SQL INSERT Statement
This query is used to insert a record into a database table.
INSERT INTO emp(empno, ename) values(101, 'John Guttag')
inserts a record in to the emp table and set its empno field to 101 and its ename field to 'John Guttag'
SQL UPDATE Statement
This query is used to modify existing records in a database table.
UPDATE emp SET ename = 'Eric Gamma' WHERE empno = 101
updates the record whose empno field is 101 by setting its ename field to 'Eric Gamma'
SQL DELETE Statement
This query is used to delete existing record(s) from a database table.
DELETE FROM emp WHERE empno = 101
Hi Pinal,
I want to understand, when we do this for millions of records as insert, which is suggestable to to use whether SSIS OLEDB destination or SQLServer MERGE Command?
Hi,
Does the merge statement works for databases placed on different servers?
Pinal, I have only inserts. But occasionally I deal with Deletes/Updates. Does the MERGE statement takes more time than INSERT Statement? Could you please let me know.
Hello I am Using sql 2008 but below query given error:
— Merge Statement
With CTE AS
(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);
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘MERGE’.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘AS’.
Hello , pleased ignore above and consider this one
below query given error , i am using sql 2008
– Merge Statement
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);
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘MERGE’.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘AS’.
Hi Pinal, I am trying to use when not matched by Target then Insert, is it possible to use a different table to insert other than the target? I want to keep the target clean and build the not matched data into a different table. someone suggested using cte, not sure how to, any suggestions are welcome, thanks
Hope for the best!!!!!
i am having a production Database and a staging database having total different table structure.
i Need if any Insert delete ,update operation is performed in production it shall be updated on the staging server.
The database of production is about 15Gb and backup for 3 years has been maintained on staging server .
Dir sir
how can i perform a SEARCH by keyword in sql server.thx
Hi Pinal, i’m trying to sync multiple tables on different database /servers. How can i perform merge operations? please help me.
As per https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 it should work cross database also. You just need to use three part naming while referencing the table. DB.Schema.Table.
Hello Sir,
Can you please tell me how to call a procedure inside the WHEN MATCHED THEN part
Hi, i have been using merged for upsert command and all the while it is ok for insert and update only. But now the destination table is growing big and the operation time is taking too long. Any way can we improve this?
Seriously, I love how great your solutions, examples and explications are, thank you!