Peter Larsson Blog

Patron Saint of Lost Yaks

Composable DML

With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement.
The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there.

With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML?
Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query!

In this example I am showing you how to audit certain records when new values arrives from a file. All error-handling code has been removed for easier reading.

Thisi is how it can look like in SQL Server 2000

-- SQL Server 2000
DECLARE     @Audit TABLE
            (
                         CustomerID INT,
                         oldValue INT,
                         newValue INT
            )

DECLARE     @Work TABLE
            (
                         CustomerID INT,
                         Value INT
            )

INSERT      @Work
SELECT      1, 10000 UNION ALL
SELECT      2, 15000 UNION ALL
SELECT      3, 12000 UNION ALL
SELECT      4, 11000 UNION ALL
SELECT      5, 19000 UNION ALL
SELECT      6, 17000

DECLARE     @File TABLE
            (
                         CustomerID INT,
                         Value INT
            )

INSERT      @File
SELECT      3, 12000 UNION ALL
SELECT      4, 15000 UNION ALL
SELECT      5, 20000 

SELECT      *
FROM        @Work

BEGIN TRANSACTION

INSERT      @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
SELECT      w.CustomerID,
            w.Value,
            f.Value
FROM        @Work AS w
INNER JOIN @File AS f ON f.CustomerID = w.CustomerID
WHERE       w.Value <> f.Value

UPDATE      w
SET         w.Value = f.Value
FROM        @Work AS w
INNER JOIN @File AS f ON f.CustomerID = w.CustomerID

ROLLBACK TRANSACTION

SELECT      *
FROM        @Audit 

SELECT      *
FROM        @Work


As you can see, you have to use two statements if you don't want to use a TRIGGER.

This is how it can be done in SQL Server 2005 using same sample data preparation as above

-- SQL Server 2005
BEGIN TRANSACTION

UPDATE      w
SET         w.Value = f.Value
OUTPUT      deleted.CustomerID,
            deleted.Value,
            inserted.Value

INTO        @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
FROM        @Work AS w
INNER JOIN @File AS f ON f.CustomerID = w.CustomerID

DELETE      a
FROM        @Audit AS a
INNER JOIN @File AS f ON f.CustomerID = a.CustomerID
WHERE       a.oldValue = a.newValue

ROLLBACK TRANSACTION

SELECT      *
FROM        @Audit

SELECT      *
FROM        @Work

As you can see, you still need two statements if you don't want to use a TRIGGER. But using a trigger is still is two statements even if the trigger runs in same context.

Let us wrap this up and demonstrate the Composable DML available in SQL Server 2008, using same sample data preparation as above

-- SQL Server 2008
INSERT      @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
SELECT      CustomerID,
            oldValue,
            newValue
FROM        (
                        UPDATE      w
                        SET         w.Value = f.Value,
                                    w.LastEdited = GETDATE()
                        OUTPUT      deleted.CustomerID,
                                    deleted.Value AS oldValue,
                                    inserted.Value AS newValue
                        FROM        @Work AS w
                        INNER JOIN @File AS f ON f.CustomerID = w.CustomerID
            ) AS d
WHERE       oldValue <> newValue

SELECT      *
FROM        @Audit

SELECT      *
FROM        @Work

Legacy Comments


sql dba
2009-04-08
re: Composable DML
Hi Peter.
Looks amazing :) insert select from update...
Assume that it's quite not easy to debug and maintain such constructions 'cause one statement makes multiple operations.
BTW i did not know about output in sql 2005.
many thanks, vadym

Adam Machanic
2009-04-08
re: Composable DML
Hi Peter,

Nice writeup, but one comment: It's probably better to put the WHERE clause (oldValue <> newValue) on the UPDATE itself rather than outside on the select. Otherwise the rows will still physically get updated -- the storage engine does not check to see whether an update is actually needed or whether the values are already set that way, so it's best to filter as much as possible on the way in rather than on the way to the audit table.

Peso
2009-04-08
re: Composable DML
Perhaps. You may want to include an UPDATE for ChgDate or similar even if new value is equal to old value.
Or maybe you want to update another column "last filename" or similar.
Then you have to update all records, but still only audit the changes.

gvee
2009-04-14
re: Composable DML
Love it!
Immediately bookmarked and added to code snippets :D

Cheap Moncler Jackets
2011-09-07
re: Composable DML
"I cannot emphasize enough to Texans in the impacted areas the importance of heeding all warnings from local officials, especially evacuation orders, as these fires are mean, swift and highly dangerous," Gov. Rick Perry said.