Tag Archives: Common Table Expressions

Composable DML (OUTPUT clause) inside of a T-SQL Common Table Expression (CTE)

Disclaimer: the following was tested on SQL Server 2008R2.

When I presented my T-SQL OUTPUT topic to the San Diego SQL Server user group last month, a question came up regarding the composable DML use case. For those of you who aren’t familiar with that term, it’s a way in which you can choose to audit only certain rows from a data-changing operation, while using the OUTPUT clause. On its own, the OUTPUT clause returns all rows affected.

So, the basic syntax would look like this:

UPDATE  MyTable
SET     ...
OUTPUT  inserted.ID,
        deleted.Amount AS OldAmount,
        inserted.Amount AS NewAmount
INTO    AuditTable
...;

When you run such a statement, a row is inserted into AuditTable for each row updated in MyTable. If you make that a sub-query, however, you can do something like this:

INSERT INTO AuditTable (...)
SELECT  ...
FROM    (
            UPDATE  MyTable
            SET     ...
            OUTPUT inserted.ID,
                deleted.Amount AS OldAmount,
                inserted.Amount AS NewAmount
        ) AS UpdatedData
WHERE  UpdatedData.OldAmount > 300;

This time around, you only have rows in AuditTable where the value of Amount was 300 before the data changed.

The question which came up is whether you can use that same arrangement, but inside a common table expression. In other words, since OUTPUT returns the equivalent of a SELECT statement, you can place that inside of a CTE, right?


WITH UpdatedData AS (
    UPDATE  MyTable
    SET     ...
    OUTPUT inserted.ID,
        deleted.Amount AS OldAmount,
        inserted.Amount AS NewAmount
)
INSERT INTO AuditTable (...)
SELECT  ...
FROM    UpdatedData
WHERE    UpdatedData.OldAmount > 300;

Well, it turns out you can’t. Unfortunately, the error message isn’t terribly helpful:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'UPDATE'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.

So, while you can do some nifty things with OUTPUT and CTEs, it looks like SQL Server doesn’t think that these are 2 great tastes that taste great together. We’ll have to leave that to the peanut butter cups.

SoCal Code Camp 2011 Sessions Added

I have just submitted 2 sessions to the Southern California Code Camp, to be held in San Diego on June 25th & 26th.

Should you be interested in either (or both) topics and will be in the area that weekend, please register on the site and vote for them.

If you’re going to be in the area that weekend, I encourage you to attend as the variety of topics is quite broad. You’ll likely find many topics of interest among the submitted session abstracts.