Click here to Skip to main content
Click here to Skip to main content

Merge Statement in SQL Server 2008

By , 10 Jun 2009
Rate this:
Please Sign up or sign in to vote.

Introduction

One of the fantastic new features of SQL Server 2008 is Merge Statement. Using a single statement, we can Add/Update records in our database table, without explicitly checking for the existence of records to perform operations like Insert or Update.

Facts about Merge Statement

Here are a few facts that you must know before starting to use Merge Statement:

  1. Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
  2. Done as a set-based operation; more efficient than multiple separate operations
  3. MERGE is defined by ANSI SQL; you will find it in other database platforms as well
  4. Useful in both OLTP and Data Warehouse environments
    OLTP: merging recent information from external source
    DW: incremental updates of fact, slowly changing dimensions.

A typical merge statement looks like:

MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

Example

-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
WHEN MATCHED THEN
    UPDATE SET
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
WHEN NOT MATCHED THEN 
      INSERT (CustID, CompanyName, Phone)
      VALUES (CT.CustID, CT.CompanyName, CT.Phone)
CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target
MERGE INTO dbo.tbl_Target AS t
    USING dbo.tbl_Source AS s    
        ON t.id = s.id
    WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
        --Row exists and data is different
        UPDATE SET t.name = s.name, t.qty = s.qty
    WHEN NOT MATCHED THEN 
        --Row exists in source but not in target
        INSERT VALUES (s.id, s.name, s.qty) 
    WHEN SOURCE NOT MATCHED THEN 
        --Row exists in target but not in source
        DELETE OUTPUT$action, inserted.id, deleted.id

Conclusion

So now with this new feature, we can implement the feature of add/insert/delete using a single statement without checking through the records.

Hope you enjoyed this article. Happy programming!!!

History

  • 11th June, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Robin_Roy
Other Brilliance Information Sdn Bhd
Malaysia Malaysia
Working as a Senior Consultant with Brilliance MSC, Malaysia.
Love to evaluate new technologies and implement the same.
Believe in sharing knowledge.

Comments and Discussions

 
GeneralMy vote of 3 PinmemberMember 782653028-Oct-13 20:41 
QuestionMissing semicolon PinmemberChaitanya.Moguluri9-May-13 4:26 
GeneralMy vote of 5 PinmemberSchehaider_Aymen3-Oct-12 6:31 
GeneralMy vote of 3 PinmemberMorteza Azizi23-Jan-12 3:05 
GeneralRe: My vote of 3 Pinmemberlosmac8-May-12 10:32 
GeneralSample code invalid Pinmemberryancrawcour1-Feb-10 14:15 
GeneralMy Vote of 5 Pinmemberjohnclark6410-Aug-09 17:05 
GeneralRe: My Vote of 5 PinmemberRobin_Roy17-Aug-09 15:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 11 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid