Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

Merge Statement in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.68/5 (20 votes)
10 Jun 2009CPOL 156.1K   26   10
Microsoft SQL Server 2008 new feature, Merge Statement

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

SQL
-- 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)
SQL
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)



Comments and Discussions

 
QuestionInsert , update rowcount in mege statememnt Pin
vipin jha11-Feb-15 0:40
vipin jha11-Feb-15 0:40 
QuestionDrawbacks of Merge Pin
Member 1108063013-Sep-14 17:39
Member 1108063013-Sep-14 17:39 
GeneralMy vote of 3 Pin
Member 782653028-Oct-13 20:41
Member 782653028-Oct-13 20:41 
QuestionMissing semicolon Pin
M S Chaitanya9-May-13 4:26
M S Chaitanya9-May-13 4:26 
GeneralMy vote of 5 Pin
Schehaider_Aymen3-Oct-12 6:31
Schehaider_Aymen3-Oct-12 6:31 
GeneralMy vote of 3 Pin
Morteza Azizi23-Jan-12 3:05
professionalMorteza Azizi23-Jan-12 3:05 
GeneralRe: My vote of 3 Pin
Maciej Los8-May-12 10:32
mveMaciej Los8-May-12 10:32 
GeneralSample code invalid Pin
ryancrawcour1-Feb-10 14:15
ryancrawcour1-Feb-10 14:15 
Hi
The sample you provide does not execute;
WHEN SOURCE NOT MATCHED THEN
--Row exists in target but not in source
DELETE OUTPUT$action, inserted.id, deleted.id

SQL complains about the "SOURCE" part. I am guessing it should reading WHEN NOT MATCHED BY SOURCE?
And what was your intention with OUTPUT$action?
SQL (and I) don't understand that syntax at all.
Nor the inserted.id, deleted.id portion

Could you please fix your sample code so that it works
GeneralMy Vote of 5 Pin
johnclark6410-Aug-09 17:05
johnclark6410-Aug-09 17:05 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 15:52
Robin_Roy17-Aug-09 15:52 

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

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