SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution

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.

SQL SERVER - Merge Operations - Insert, Update, Delete in Single Execution MergeStatement

Let us check the execution plan for the merge operator. You can click on following image to enlarge it.

SQL SERVER - Merge Operations - Insert, Update, Delete in Single Execution merge1

Let us evaluate the execution plan for the Table Merge Operator only.

SQL SERVER - Merge Operations - Insert, Update, Delete in Single Execution merge2

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)

, ,
Previous Post
SQL SERVER – Subquery or Join – Various Options – SQL Server Engine Knows the Best – Part 2
Next Post
SQL SERVER – Difference Between GETDATE and SYSDATETIME

Related Posts

51 Comments. Leave new

  • IS THIS POSSIBLE TO CREATE INSERT, UPDATE AND DELETE IN A SINGLE TRIGGER?

    Reply
  • 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?

    Reply
  • 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

    Reply
  • 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?

    Reply
  • Hi,
    Does the merge statement works for databases placed on different servers?

    Reply
  • 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.

    Reply
  • 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’.

    Reply
  • 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’.

    Reply
  • 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

    Reply
  • 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 .

    Reply
  • Dir sir
    how can i perform a SEARCH by keyword in sql server.thx

    Reply
  • Hi Pinal, i’m trying to sync multiple tables on different database /servers. How can i perform merge operations? please help me.

    Reply
  • Hello Sir,
    Can you please tell me how to call a procedure inside the WHEN MATCHED THEN part

    Reply
  • 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?

    Reply

Leave a Reply

Menu