Resources For IT Professionals
Post an article
Wikis - Page Details
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SQL Server 2008+: Merge (Unity is Strength - Strengthen your Performance by Merge)

SQL Server 2008+: Merge (Unity is Strength - Strengthen your Performance by Merge)

SQL Server 2008+: Merge (Unity is Strength - Strengthen your Performance by Merge)

Table of Contents

Problem Definition


In SQL Server for each DML transaction (Insert, Update, Delete) we need to write separate statements which will be executed one by one in SQL Server execution engine.
Let's give a basic example that, if we are passing a record to procedure, if the record already exists, then we need to update or else it should be inserted. In this scenario, we need to write 1 DML statement to check the record's existence and 2 DML statements  based on the 1st query result, which will be a performance hurdle.

Solution

Simply we can say "Unity is strength". Like that "MERGE" your DML and DDL statements together to break the performance hurdles.


MERGE

MERGE is a new feature in SQL Server 2008 (and up) that provides an efficient way to perform multiple DML operations.

Example
Let's assume, we are going to write a procedure for this payroll scenario.
    1. If employee exists and salary is less than 100, then delete that employee from payroll
    2. If employee exists and salary is greater than 100, then update (add extra amount as salary / 100)
    3. if employee does not exist, then insert the employee with salary 2500.

In prior to SQL Server 2008 version, we would need to write multiple DML statements.
But in 2008 (and up) we can use MERGE as follows: (The details of the query added as comments.)

DECLARE @PayRoll TABLE(EmpID INT, Salary INT, AddAmt INT) -- Declare table

INSERT INTO @PayRoll
VALUES(1,2500,0),(2,100,0),(3,2700,0) --Insert sample records

MERGE @PayRoll AS PayRoll
USING (SELECT EmpID,Salary FROM @PayRoll) AS PayRollAd -- Update the payroll table by taking values from that same table
ON PayRoll.EmpID = PayRollAd.EmpID
WHEN MATCHED AND PayRoll.Salary < 100 THEN DELETE -- If employee salary less than 100 then delete
WHEN MATCHED THEN UPDATE SET PayRoll.AddAmt = (PayRoll.Salary / 10) -- If employee salary greater than 100 then update the additional amount
WHEN NOT MATCHED THEN
INSERT(EmpID, Salary)
VALUES (PayRollAd.EmpID,2500); -- if employee is not there then insert (this scenario will come only when we use different table for merge)

SELECT * FROM @PayRoll

 

Points to note

Semicolon is mandatory after the merge statement. This is very important and it is an often forgotten fact!

When there is a MATCH clause used along with some condition, it has to be specified first among all other WHEN MATCH clauses.


Conclusion

MERGE statement improves the performance of database as it passes through data only once.


References
, , , , , , , , [Edit tags]
Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 31 Aug 2013 12:34 AM

    Balaji M Kundalam edited Revision 8. Comment: Typography - minor correction

  • 29 Aug 2013 10:54 AM

    Naomi  N edited Revision 6. Comment: Minor edit

  • 29 Aug 2013 10:47 AM

    Maheshkumar S Tiwari edited Revision 5. Comment: Added Tag and formatting

  • 29 Aug 2013 10:45 AM

    Maheshkumar S Tiwari edited Revision 4. Comment: Added TOC and formatting

  • 21 Aug 2013 8:18 AM

    Naomi  N edited Revision 2. Comment: Added link to Alexander Kuznetsov article

  • 21 Aug 2013 8:15 AM

    Naomi  N edited Revision 1. Comment: Minor edit

Page 1 of 1 (6 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 31 Aug 2013 12:34 AM

    Balaji M Kundalam edited Revision 8. Comment: Typography - minor correction

  • Posted by
    on 29 Aug 2013 10:54 AM

    Naomi  N edited Revision 6. Comment: Minor edit

  • Posted by
    on 29 Aug 2013 10:47 AM

    Maheshkumar S Tiwari edited Revision 5. Comment: Added Tag and formatting

  • Posted by
    on 29 Aug 2013 10:45 AM

    Maheshkumar S Tiwari edited Revision 4. Comment: Added TOC and formatting

  • Posted by
    on 21 Aug 2013 8:20 AM

    BTW, a bit funny example - why would we delete underpaid employees from the Payroll?

  • Posted by
    on 21 Aug 2013 8:18 AM

    Naomi  N edited Revision 2. Comment: Added link to Alexander Kuznetsov article

  • Posted by
    on 21 Aug 2013 8:16 AM

    Nice and simple article - good for beginners

  • Posted by
    on 21 Aug 2013 8:15 AM

    Naomi  N edited Revision 1. Comment: Minor edit

Page 1 of 1 (8 items)