Composable DML

Another powerful T-SQL enhancement in SQL Server 2008 is the ability to write composable DML. It allows to consume the OUTPUT clause result set and to feed it as source for a query.

Here is one example to demonstrate that. The scenario is to merge data from daily updated branch office table to central office static table. Additional requirement is to store auditing data for any changes in the company name column. In prior versions of SQL Server this would require a couple SQL statements to accomplish. In SQL Server 2008 the new MERGE statement allows to perform all merge actions in a single statement, and then feed via the OUTPUT clause data for the auditing table. That way a single SQL statement completes the task.

-- Create central accounts static table

CREATE TABLE CentralOfficeAccounts (

 account_nbr INT PRIMARY KEY,

 company_name VARCHAR(35),

 primary_contact VARCHAR(35),

 contact_phone VARCHAR(12));

 

-- Daily updated branch data

CREATE TABLE BranchOfficeAccounts (

 account_nbr INT PRIMARY KEY,

 company_name VARCHAR(35),

 primary_contact VARCHAR(35),

 contact_phone VARCHAR(12));

 

-- Insert sample data

INSERT INTO CentralOfficeAccounts

VALUES (1, 'Bayside Motors', 'Jim Smith', '902-203-1234'),

       (2, 'Dallas Industries', 'Joe Doe', '301-663-9134'),

       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053');

 

INSERT INTO BranchOfficeAccounts

VALUES (2, 'Dallas Industries, Inc.', 'Rick Gross', '301-663-9134'),    -- changed

       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053'),       -- same

       (4, 'Auto Insurance Co.', 'Chris Jefferson', '313-601-6201');    -- new

 

-- Table for auditing

CREATE TABLE AccountsAudit (

 account_nbr INT,

 change_action NVARCHAR(10),

 change_date DATETIME DEFAULT CURRENT_TIMESTAMP,

 old_company_name VARCHAR(35),

 new_company_name VARCHAR(35),

 PRIMARY KEY(account_nbr, change_action, change_date));

 

-- Single SQL statement performing the merge and auditing actions

INSERT INTO AccountsAudit

 (account_nbr, change_action, old_company_name, new_company_name)

SELECT account_nbr, merge_action, old_company_name, new_company_name

FROM (MERGE INTO CentralOfficeAccounts AS C   

      USING BranchOfficeAccounts AS B       

        ON C.account_nbr = B.account_nbr

      WHEN MATCHED                       

       AND C.company_name <> B.company_name THEN                       

           UPDATE SET C.company_name = B.company_name

      WHEN NOT MATCHED THEN           

           INSERT (account_nbr, company_name,

                   primary_contact, contact_phone)

           VALUES (B.account_nbr, B.company_name,

                   B.primary_contact, B.contact_phone)

    WHEN SOURCE NOT MATCHED THEN       

          DELETE

    OUTPUT $action,

            COALESCE(inserted.account_nbr, deleted.account_nbr),

            deleted.company_name,

            inserted.company_name

    ) AS T(merge_action, account_nbr, old_company_name, new_company_name);

 

SELECT account_nbr, change_action, change_date,

       old_company_name, new_company_name

FROM AccountsAudit;

 

/*

 

-- Results

account_nbr change_action change_date old_company_name   new_company_name

----------- ------------- ----------- ------------------ -----------------------

1           DELETE        2008-04-15 Bayside Motors     NULL

2           UPDATE        2008-04-15 Dallas Industries Dallas Industries, Inc.

4           INSERT        2008-04-15 NULL               Auto Insurance Co.

 

*/

Note:

This code has been tested with SQL Server 2008 CTP 6 (February 2008). As of SQL Server 2008 Release Candidate 0 (June 2008) the clause “WHEN SOURCE NOT MATCHED” has been replaced with “WHEN NOT MATCHED BY SOURCE”. This makes the wording clearer. It is good to note that this clause of MERGE is not standard.

2 replies
  1. Russ
    Russ says:

    Doesn’t quite work: the change is to the contact, but the update is to the company name. Don’t you need to set the contact as well, and the date, to pick up all the changes? Or am I lost, as usual? Yes the demo of the Audit works, but not the UPDATE.

    Reply
  2. Plamen Ratchev
    Plamen Ratchev says:

    Hi Russ,

    It actually works as intended. The point here was to demonstrate how to implement auditing for the changes to the company name, not to other columns. The example can be extended to log changes to all columns but that would be simply adding more columns to the logging table and more conditions to the WHEN MATCHED clause.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.