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:
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!!
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