Click here to Skip to main content
11,933,621 members (54,665 online)
Click here to Skip to main content
Add your own
alternative version


11 bookmarked

Composable DML and Merge Statement in SQL Server 2008

, 10 Aug 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Simple example to show how Composable DML works in SQL Server 2008.


In my article Merge Statement in SQL Server 2008, I tried to showcase the new features of SQL Server 2008 wherein 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.

In this article, let me explain Composable DML, another cool feature of SQL Server 2008.

Here are a few facts that you must know before starting to use Composable DML:

  • Combine multiple DML actions in one statement.
  • Have one statement operate on rows affected by another.
  • SQL Server 2008 introduces INSERT FROM DML.


INSERT INTO [target table]
FROM ([INSERT | UPDATE | DELETE | MERGE statement with OUTPUT clause]) AS D 


There is a Customer table in Head Office and also in Branch Office. Through End of Day processing, the Head Office table is updated with the Branch Office table data for changes like new customer registration and changes in existing customer info. The changes (New, Edit) are logged in an Audit table.

The example uses a Merge statement and Composable DML to perform update in the Head Office Customer table and also logs the updates in the Audit table.

-- Create Head Office Customer Table
CREATE TABLE tblCustomerMaster_HeadOffice(
 CustomerName VARCHAR(100),
 PrimaryContact VARCHAR(100),
 ContactPhone VARCHAR(12));
-- Create Branch Office Customer Table
CREATE TABLE tblCustomerMaster_BranchOffice(
 CustomerName VARCHAR(100),
 PrimaryContact VARCHAR(100),
 ContactPhone VARCHAR(12));
-- Create Audit Table
CREATE TABLE tblCustomersAudit (
 CustomerID INT,
 ChangeAction NVARCHAR(10),
 OldCustomerName VARCHAR(100),
 NewCustomerName VARCHAR(100),
 PRIMARY KEY(CustomerID , ChangeAction , ChangeDate ));
-- SQL Statement to Sync the Head Office Customers Table
-- using Brach Office Customers Table and 
-- Log into the CustomersAudit Table
INSERT INTO tblCustomersAudit(CustomerID, ChangeAction, 
            OldCustomerName, NewCustomerName) 
SELECT CustomerID, MergeAction, OldCustomerName, NewCustomerName
FROM (    

    MERGE INTO tblCustomerMaster_HeadOffice AS HOC   
        USING tblCustomerMaster_BranchOffice AS BOC        
            HOC.CustomerID = BOC.CustomerID
    WHEN MATCHED AND HOC.CustomerName <> BOC.CustomerName
    -- Record exists, data different
            UPDATE SET HOC.CustomerName = BOC.CustomerName, 
            HOC.PrimaryContact = BOC.PrimaryContact, 
            HOC.PrimaryContact = BOC.ContactPhone
    WHEN NOT MATCHED --Record in Branch Office and Not in Head Office
       INSERT (CustomerID, CustomerName, PrimaryContact, ContactPhone)
       VALUES (BOC.CustomerID, BOC.CustomerName, 
               BOC.PrimaryContact, BOC.ContactPhone)

    OUTPUT $action, 
        COALESCE(inserted.CustomerID, deleted.CustomerID),
 ) AS T(MergeAction, CustomerID, OldCustomerName, NewCustomerName)


I tried to showcase Composable DML in action along with Merge statements. This is pretty simple and easy to implement.

Hope you enjoyed this article. Happy programming!!!


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

GeneralWell approach Pin
Md. Marufuzzaman21-Aug-09 4:31
groupMd. Marufuzzaman21-Aug-09 4:31 
This is excellent...Smile | :) 5 from me.

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.

Md. Marufuzzaman

GeneralRe: Well approach Pin
Robin_Roy24-Aug-09 16:46
memberRobin_Roy24-Aug-09 16:46 
GeneralNice article.. Pin
Taslima Bagum17-Aug-09 6:46
memberTaslima Bagum17-Aug-09 6:46 
GeneralRe: Nice article.. Pin
Robin_Roy17-Aug-09 16:45
memberRobin_Roy17-Aug-09 16:45 
QuestionMoney Sign ($)? Pin
aspdotnetdev16-Aug-09 11:58
memberaspdotnetdev16-Aug-09 11:58 
AnswerRe: Money Sign ($)? Pin
Robin_Roy16-Aug-09 18:38
memberRobin_Roy16-Aug-09 18:38 
GeneralRe: Money Sign ($)? Pin
aspdotnetdev17-Aug-09 21:04
memberaspdotnetdev17-Aug-09 21:04 
GeneralHelpful Pin
arvindjo12-Aug-09 0:38
memberarvindjo12-Aug-09 0:38 
GeneralRe: Helpful Pin
Robin_Roy17-Aug-09 16:45
memberRobin_Roy17-Aug-09 16:45 
GeneralGood Article Pin
arvindcoolest10-Aug-09 18:16
memberarvindcoolest10-Aug-09 18:16 
GeneralRe: Good Article Pin
Robin_Roy17-Aug-09 16:46
memberRobin_Roy17-Aug-09 16:46 
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:46
memberRobin_Roy17-Aug-09 16:46 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.151126.1 | Last Updated 10 Aug 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid