Click here to Skip to main content
15,940,550 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 157K   26   10
Microsoft SQL Server 2008 new feature, Merge Statement


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>


-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
      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 =
    WHEN MATCHED AND ( != OR t.qty!= s.qty) THEN
        --Row exists and data is different
        UPDATE SET =, t.qty = s.qty
        --Row exists in source but not in target
        INSERT VALUES (,, s.qty) 
        --Row exists in target but not in source
        DELETE OUTPUT$action,,


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!!!


  • 11th June, 2009: Initial post


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