Sql server® 2012 t-sql fundamentals

Composable dML

The OUTPUT clause returns an output row for every modified row. But what if you need to direct 

only a subset of the modified rows to a table, perhaps for auditing purposes? SQL Server supports a 

feature called composable DML that allows you to directly insert into the final target table only the 

subset of rows that you need from the full set of modified rows.

To demonstrate this capability, first create a copy of the Products table from the Production 

schema in the dbo schema, as well as the dbo.ProductsAudit table, by running the following code.

IF OBJECT_ID('dbo.ProductsAudit', 'U') IS NOT NULL DROP TABLE dbo.ProductsAudit; 

IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products; 

 

CREATE TABLE dbo.Products 

( 

  productid    INT          NOT NULL, 

  productname  NVARCHAR(40) NOT NULL, 

  supplierid   INT          NOT NULL, 

  categoryid   INT          NOT NULL, 

  unitprice    MONEY        NOT NULL 

    CONSTRAINT DFT_Products_unitprice DEFAULT(0), 

  discontinued BIT          NOT NULL  

    CONSTRAINT DFT_Products_discontinued DEFAULT(0), 

  CONSTRAINT PK_Products PRIMARY KEY(productid), 

  CONSTRAINT CHK_Products_unitprice CHECK(unitprice >= 0) 

); 

 

INSERT INTO dbo.Products SELECT * FROM Production.Products; 

 

CREATE TABLE dbo.ProductsAudit 

www.it-ebooks.info

---

286  

Microsoft SQL Server 2012 T-SQL Fundamentals

( 

  LSN INT NOT NULL IDENTITY PRIMARY KEY, 

  TS  DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP), 

  productid INT NOT NULL, 

  colname SYSNAME NOT NULL, 

  oldval SQL_VARIANT NOT NULL, 

  newval SQL_VARIANT NOT NULL 

);

Suppose that you now need to update all products that are supplied by supplier 1, increasing their 

price by 15 percent. You also need to audit the old and new values of updated products, but only 

those with an old price that was less than 20 and a new price that is greater than or equal to 20.

You can achieve this by using composable DML. You write an UPDATE statement with an OUTPUT 

clause and define a derived table based on the UPDATE statement. You write an INSERT SELECT 

statement that queries the derived table, filtering only the subset of rows that is needed. Here’s the 

complete solution code.

INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval) 

  SELECT productid, N'unitprice', oldval, newval 

  FROM (UPDATE dbo.Products 

          SET unitprice *= 1.15 

        OUTPUT  

          inserted.productid, 

          deleted.unitprice AS oldval, 

          inserted.unitprice AS newval 

        WHERE supplierid = 1) AS D 

  WHERE oldval < 20.0 AND newval >= 20.0;

Recall earlier discussions in the book about logical query processing and table expressions—the 

multiset output of one query can be used as input to subsequent SQL statements. Here, the output of 

the OUTPUT clause is a multiset input for the SELECT statement, and then the output of the SELECT 

statement is inserted into a table.

Run the following code to query the ProductsAudit table.

SELECT * FROM dbo.ProductsAudit;

You get the following output.

LSN TS                        ProductID   ColName     OldVal   NewVal 

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

1   2008-08-05 18:56:04.793   1           unitprice   18.00    20.70 

2   2008-08-05 18:56:04.793   2           unitprice   19.00    21.85

Three products were updated, but only two were filtered by the outer query; therefore, only those 

two were audited. 

www.it-ebooks.info

---