T-SQL Trick

Leaned a new trick in T-SQL the other day and thought I’d post it here.

Turns out, you can use the Output clause from Insert/Update/Delete/Merge statements as a data-source for Insert-Select statements.

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

create table #T1 (
Act varchar(100),
Val int);

create table #T2 (
Col1 int);

insert into #T1 (Act, Val)
select ActM, ValM
from (
merge into #T2 as Tgt
using (select 1 as ColA) as Src
on Tgt.Col1 = Src.ColA
when not matched by target then insert (Col1) values (Src.ColA)
output $action, inserted.Col1) as MergeChanges (ActM, ValM);

select *
from #T1;

select *
from #T2;

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

The Output clause generates a dataset, which allows for the $action variable for Merge statements and so on, which can then be named (“as MergeChanges”) and given column names (“(ActM, ValM)”).  I used “ActM” to differentiate the “Action Merge” from the “Act” column of the #T1 table.

The uses for logging data changes are obvious.

Avoid the extra steps for Output Into, and bypasses the rules that Output Into is subject to on the characteristics of the target table.  It’s just an Insert-Select at that point, and subject only to those rules, which are much more usual.

This only works in Compatibility 100 (and I assume in Denali, which is probably 110, but I haven’t tried that yet).  Won’t work in databases with lower compatibility, even in SQL 2008 R2 servers.  They’ll let you use Merge, and even compatibility 80 (SQL 2000) will allow Output, but they won’t allow this particular trick.

Just picked this one up a few days ago and thought it was pretty cool.