THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Exploring Composable DML

SQL Server 2008 includes a new feature called “Composable DML” which I hadn’t heard about until I read Adam’s post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE. Composable DML is a new feature in T-SQL that allows you to use the data provided by the OUTPUT clause of a DML (i.e. INSERT, UPDATE, DELETE) statement as a derived table and thus insert it elsewhere. Here I’ll attempt to demo this in as simple a way as possible:


create table #t1
(
name varchar(max)
);
go


DECLARE @rowcounts TABLE
(
name varchar(max)
, rowcnt int
);


insert @rowcounts
select x.name, x.rowcnt
from (
insert #t1
output inserted.name, 1 as rowcnt
select 'Henry' as name
) x;


Take a moment to browse that code because its very easy to understand. We simply take the data from the OUTPUT clause of our insertion and then insert it into a table variable. Very handy indeed.

 

 

Unfortunately that’s just about all you can do with it. For example, do you want to examine the data in SSMS without having to insert it somewhere first? No can do! Observe:


create table #t2
(
name varchar(max)
);
go


DECLARE @rowcounts TABLE
(
name varchar(max)
, rowcnt int
);


select x.name, x.rowcnt
from (
insert #t2
output inserted.name, 1 as rowcnt
select 'Henry' as name
) x;


Note on this occasion we’re not inserting the data anywhere and hence we get an error:

image

So, the only thing we can do with that data is insert it somewhere – we can’t even do a simple SELECT. Seems like a pretty strange restriction to me!!

 

 

Lastly, let’s try aggregating it (note the GROUP BY clause):

create table #t3
(
name varchar(max)
);
go


DECLARE @rowcounts TABLE
(
name varchar(max)
, rowcnt int
);

insert @rowcounts
select x.name, sum(x.rowcnt)
from (
insert #t3
output inserted.name, 1 as rowcnt
select 'Henry' as name
) x
group by x.name;


Nope, can’t do that either!!

image

 

 

So, ostensibly Composable DML seems like a very nifty new trick for your bag but in its current form it is fairly limited. Let’s hope it improves in the next version!

@Jamiet

Published Monday, August 31, 2009 12:33 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

DB2 appears to have more complete support for SELECT from DML changes. It supports the following:

SELECT <columns> FROM OLD TABLE ( <DML statement> )

WHERE ...

where OLD may be replaced with NEW or FINAL.

August 31, 2009 5:03 PM
 

Girish said:

OUTPUT clause is meant as a select and it definitely can be used in the manner below for selecting the data before it gets inserted:

CREATE TABLE #t2 ( name VARCHAR(MAX) ) ;

GO

DECLARE @rowcounts TABLE

   (

     name VARCHAR(MAX) ,

     rowcnt INT

   ) ;

INSERT  #t2

OUTPUT  inserted.name, 1 AS rowcnt

       SELECT  'Henry' AS name        

DROP TABLE #t2        

I hope this is what you were looking for when trying to select but sure GROUP BY is not possible.

June 8, 2010 8:01 AM
 

SSIS Junkie said:

I recently inherited a data warehousing SQL Server solution from my good buddy James Rowland-Jones (you

July 13, 2012 9:09 AM
 

SSIS Junkie said:

In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called

February 7, 2013 8:59 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement