Click here to Skip to main content
13,249,813 members (83,183 online)
Click here to Skip to main content
Add your own
alternative version


25 bookmarked
Posted 10 Jun 2009

Merge Statement in SQL Server 2008

, 10 Jun 2009
Rate this:
Please Sign up or sign in to vote.
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)


About the Author

You may also be interested in...


Comments and Discussions

QuestionInsert , update rowcount in mege statememnt Pin
vipin jha11-Feb-15 1:40
membervipin jha11-Feb-15 1:40 
QuestionDrawbacks of Merge Pin
Member 1108063013-Sep-14 18:39
memberMember 1108063013-Sep-14 18:39 
GeneralMy vote of 3 Pin
Member 782653028-Oct-13 21:41
memberMember 782653028-Oct-13 21:41 
QuestionMissing semicolon Pin
Chaitanya.Moguluri9-May-13 5:26
memberChaitanya.Moguluri9-May-13 5:26 
GeneralMy vote of 5 Pin
Schehaider_Aymen3-Oct-12 7:31
memberSchehaider_Aymen3-Oct-12 7:31 
GeneralMy vote of 3 Pin
Morteza Azizi23-Jan-12 4:05
memberMorteza Azizi23-Jan-12 4:05 
GeneralRe: My vote of 3 Pin
losmac8-May-12 11:32
memberlosmac8-May-12 11:32 
GeneralSample code invalid Pin
ryancrawcour1-Feb-10 15:15
memberryancrawcour1-Feb-10 15:15 
GeneralMy Vote of 5 Pin
johnclark6410-Aug-09 18:05
memberjohnclark6410-Aug-09 18:05 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 16:52
memberRobin_Roy17-Aug-09 16: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 11 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid