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 (aka SQLChicken).

I have been very active using these Merge operations in my development. However, I have found out from my consultancy 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 table.

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 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 (http://blog.SQLAuthority.com)

About these ads

48 thoughts on “SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution

  1. if it’s possible to avoid using (if exists command) for same table

    ————–

    if exists ( select * from TesterTBL )
    update TesterTBL …
    else
    insert TesterTBL …

    —————————-

    but if we use MERGE=> Insert (NOT MATCHED situation) not happend

    example:
    —————————————————–
    MERGE INTO TesterTBL AS Target

    USING (SELECT id, val FROM TesterTBL where id=1) AS Source

    ON ( Target.id =Source.id )

    WHEN MATCHED THEN
    UPDATE SET Target.val = “MyUpdate”

    WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, val)
    VALUES (2,’MyInsert’) ;

  2. Pinal,

    I have found Merge to be very useful. Where I work we receive large files (100+ million rows), but the majority of the data hasn’t changed. We usually dump the files into a table and then compare the tables to find the deltas. I am looking for a better/more efficient way to merge two large tables. (hopefully in a single pass) I appreciate any suggestions you have.

    We currently use code similar to this:

    declare @changes table (col1 int not null primary key clustered, col2 int, col3 int, col4 int, col5 int, MergeAction char(1) not null)

    –delta updates and inserts
    insert into @changes (col1, col2, col3, col4, col5, MergeAction)
    select col1, col2, col3, col4, col5, ‘A’ as MergeAction
    from CurrentFile
    except
    select col1, col2, col3, col4, col5, ‘A’ as MergeAction
    from CurrentTable

    –deletes (pk no longer in the file)
    select col1, ‘D’ as MergeAction
    from CurrentTable
    except
    select col1, ‘D’ as MergeAction
    from CurrentFile

    merge CurrentTable as ct
    using @changes as c
    on c.col1 = ct.col1
    when matched and c.MergeAction = ‘A’ then
    update
    set ct.col2 = c.col2,
    ct.col3 = c.col3,
    ct.col4 = c.col4,
    ct.col5 = c.col5
    when matched and c.MergeAction = ‘D’ then
    delete
    when not matched by target and c.MergeAction = ‘A’ then
    insert (col1, col2, col3, col4, col5)
    values (c.col1, c.col2, c.col3, c.col4, c.col5)

    • Have you tried to use MERGE in classic description?
      Why do you do the intermediate calculations, it is faster?

      • Yes we have. The problem is that MERGE doesn’t have any way to tell if a row is identical in both tables or if it has changed. (I want similar functionality to EXCEPT built in) If you MERGE two tables that have a identical row that row will still get updated. This causes problems when using MERGE large tables, because there is a lot of extra/unneeded overhead updating rows that match. If your table is replicated that compounds the problem.

        • can’t you compare all columns in the when matched section to see if any have changed before doing update? This would eliminate the update if nothing has changed.

          • I realized something from the example Pinal had. You can specify more than one WHEN MATCHED clauses, which means you can write a WHEN MATCHED AND srcTbl.col1 tgtTbl.col1 THEN… WHEN MATCHED AND srcTbl.col2 tgtTbl.col2 THEN… and so on….

            Thanks for the lightbulb moment

  3. Dear Sir,

    I want to merge two / three huge databases of SQL server 2005 in single database with the help of script.

    Pls. help me

    Kapil

  4. Hi,

    We are using triggers to sync tables between our source and staging tables. As we are migrating to SQL 2008, I need to change my triggers to use Merge statements rather than each individual DML statements to check whether data is modified or is new. Any suggestions or tips is much appreciated how to go about for this kind of operation, how can I make use of inserted or deleted tables in my Merge statements.

    Thanks

    Munaf

  5. Dear Sir,

    I was trying to execute merge query in Mysql Query Browser (Version 1.2.5 Beta) but its giving error message like this

    “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘MERGE studenttotalmarks AS stm
    USING (SELECT StudentID,StudentName FROM student’ at line 1″

    Please reply me

    • @Gangadhara B S. Merge was added to ANSI SQL in 2003, only some database engines support it: Microsoft and Oracle both support Merge. MySQL has Replace statement which allows for something similar, but it forces a delete and then an inset of the new values. I don’t know how this would perform in the case that only ‘a few’ records are updated but many are unchanged. Does anyone know if this does a delete/update for every unchanged row?

  6. If I use merge statement only for Insert then does it is not good as use Insert statement. Please give your expert comments which one give better performance.

  7. Great blog Pinal ! It has helped me a lot. One question. I’m trying to find information on whether I can run upsert sentences affecting other tables instead of Target/Source ones inside the Merge query and I’m still not sure if I can or not. I want to accomplish something like:

    MERGE table1 AS stm
    USING table2 AS sd
    ON ID1=ID2
    WHEN MATCHED BY TARGET THEN
    DELETE table1
    DELETE table3
    WHEN NOT MATCHED THEN
    INSERT(table1_field1,table1_field2) –insert into table1
    VALUES(11,12)
    INSERT(table3_field1,table3_field2) –insert into table3
    VALUES(31,32);

    • if you’re talking about remote databases on linked servers, or a different database on the same server, you need to create a select statement and use that as a derived table for the source.
      For example,

      MERGE ThisDatabase.dbo.Student AS TargetTable
      USING (SELECT StudentID, FirstName, LastName FROM LinkedServer.OtherDatabase.dbo.Student) AS SourceTable
      ON TargetTable.StudentID = SourceTable.StudentID

      • Sorry I misread the “2005″ as 2008. There is no merge feature in 2005. You will have to come up with some creative DML logic.

  8. hello,

    I want to know if it this is possible: data is written to table ’1′, I would like this to trigger an insert statement on table ’2′. Using the below statement I created a trigger that will update values in table ’2′ from table ’1′. But this only seems to work if the entries already exist in each table and I am updated data.

    Create trigger [dbo].[updatename] on [dbo].[table1]
    for update
    as

    IF update (name)
    begin
    declare @name varchar(20)
    select @name = (select isnull(name,”)from inserted)
    update TABLE2
    set TABLE2.name = @name
    from TABLE2, inserted
    where TABLE2.ID = inserted.ID
    end
    GO

    Thanks!

  9. Hi.
    I have a problem with merge and after insert trigger on target table.
    I have the following not matched block:
    WHEN NOT matched BY target AND EXISTS(SELECT p.id FROM dbo.Podjetja p WHERE p.ds = ‘si’ + t.davcna)
    THEN
    INSERT (podjetje_id, trr, datum_odprtja, datum_zaprtja, zaprt, rez, datum_spr, tip_id)
    VALUES ((SELECT TOP 1 p.id FROM dbo.Podjetja p WHERE p.ds = ‘si’ + s.davcna),
    s.trr, s.dOdprt, s.dZaprt,
    CASE s.sSpre
    WHEN ‘Z’ THEN 1
    ELSE 0
    END,
    CASE s.eno
    WHEN NULL THEN 0
    ELSE 1
    END, s.dSpre, s.vr);
    The after insert trigger is called and the inserted record is empty. Actually the not matched addition condition (exists) should not be called at all.

    Is there a way to insert to the target with subqueries?

    thx in advance

    • I found out that on the target table, when merge on large amount of records, the corresponding after trigger is fired only once. Although the data is populated in the target trigger (which means that the trigger should have been executed ok).
      My after trigger executes some insert statements on other tables (mainly for somekind of logging).
      I checked it using print statements after getting data from the inserted and deleted record (in case of after update trigger):
      select @old_date = date1, @old_id = fkid from deleted;
      select @new_date = date1, @new_id = fkid from inserted;
      print ‘my trigger started id old’ + str(@new_id)
      print cast(@old_date as char)
      print cast(@new_date as char)
      —- some other statements follow.
      Please help. Urgent.
      thx

  10. Pingback: SQL SERVER – MERGE or INSERT, UPDATE, DELETE – Quiz – Puzzle – 19 of 31 « SQL Server Journey with SQL Authority

  11. Hi,

    In one of our application, we do lot of delete and insert to avoid Update (in Transaction tables). This delete and insert happens in threads. If we use Merge, will it lock table and cause timeout? Please suggest

    Thanks.

  12. Hello,

    I have used Merge statement for insert update in my stored procedure but I stuck upon one problem. How can I insert identity column in insert statement.

    Here is my query for your reference:

    MERGE [dbo].[tReserveData_4541] AS Target
    USING (SELECT * FROM [dbo].[tblcangrowhitesh]) AS Source
    ON ( [Source].[PK_ID] = [Target].[PK_ID])
    WHEN MATCHED THEN
    UPDATE SET [Target].[mgender] = Source.[mgender]
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([PK_ID],[Granularity],[ROWID],[mgender],[mma1],[mma2],[mma3],[mmadmincost],[mmcumulativevolume],[mmcurrency],[mmdate],[mmfileimporteddate],[mmfilename])
    VALUES ([Source].[PK_ID],[Source].[Granularity],[Source].[ROWID],[Source].[mgender],[Source].[mma1],[Source].[mma2],[Source].[mma3],[Source].[mmadmincost],[Source].[mmcumulativevolume],[Source].[mmcurrency],[Source].[mmdate],[Source].[mmfileimporteddate],[Source].[mmfilename])

    As you can see I am going to insert identity column [PK_ID] in merge-insert statement. But I am unable to do so.

    Please guide me!

    Thanks,
    Hitesh

  13. Nice, work….

    To bad i have to use this [word removed] at my work…. not very professional….

    I hope a lot of companies will download just a free Db … that use this [word removed]…

  14. I am using a Stored procedure with merge statement.How i can get the number of rows inserted/Updated from the stored proc??

  15. 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?

  16. 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

  17. 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?

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

  19. 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.

  20. 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’.

  21. 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’.

  22. 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

  23. 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 .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s