On the OUTPUT of a data modification

or “Who needs a trigger anyway?”

The output clause was, I think, one of those wonderful features of SQL 2005 that very few people used, myself included. Now in 2008, it’s even better, but still doesn’t appear to be widely used.

The output clause can be used to get, as a resultset, data from the inserted and deleted tables that are usually only visible in a trigger. As a very simple example:

Create Table #Testing (
id int identity,
somedate datetime default getdate()
)

insert into #Testing
output inserted.*
default values 

Neat. We can get back the inserted values as a result set. We can also insert them into a table variable for later processing. Using the same temp table

declare @OutputTable TABLE (id int, somedate datetime)

insert into #Testing
output inserted.* into @OutputTable
default values

select * from @OutputTable 

Very neat. Now how about a practical example? Say we have the following three tables in a database.

Create Table ParentTable (
ID int identity primary key,
ParentDescription varchar(50),
CreationDate DATETIME DEFAULT GETDATE()
)
GO
CREATE TABLE ChildTable (
ID Int identity Primary Key,
ParentID int not null constraint fk_parent foreign key references ParentTable(ID),
Somedescription varchar(20),
SomeValue Money
)
GO

Create Table AuditTable (
AuditID int identity primary key,
ChildID int,
SomeValue Money,
InsertDate DATETIME DEFAULT GETDATE(),
OriginatingLogin VARCHAR(50) DEFAULT ORIGINAL_LOGIN()
)

We get a set of data (perhaps in a temp table, perhaps in an xml document) that needs to be inserted into those tables. The source data will have multiple parent rows, each with multiple child rows. Those need to be inserted into the appropriate tables and the foreign keys have to be assigned correctly. In addition, the ID of the child rows, along with the value and the current date must be written into an audit table, along with the login name of the current user.

It’s not a difficult requirement, but because the IDs are assigned when the insert happens, it requires the data be selected back, or the use of a trigger. @@identity (or the other identity functions) can’t be used because there will be multiple rows.

So, how will the output clause help us here? First, for SQL 2005.

Source data:

CREATE TABLE #SourceData (
ParentDescription Varchar(50),
ChildDescription varchar(50),
TheValue Money
)

insert into #SourceData (ParentDescription, ChildDescription, TheValue)
values ('aaa', 'a1', 1.02)
insert into #SourceData (ParentDescription, ChildDescription, TheValue)
values ('aaa', 'a2', 58.2)
insert into #SourceData (ParentDescription, ChildDescription, TheValue)
values ('aaa', 'b1', 18.42)
insert into #SourceData (ParentDescription, ChildDescription, TheValue)
values ('bbb', 'c1', 0.59)
insert into #SourceData (ParentDescription, ChildDescription, TheValue)
values ('ccc', 'z4', 78.25)
insert into #SourceData (ParentDescription, ChildDescription, TheValue)
values ('ccc', 'z5', 85.2) 

Insert code:

DECLARE @Parent Table (id int, Descr Varchar(50))
DECLARE @Child TABLE (id int, parentID int, SomeValue money)

INSERT INTO ParentTable (ParentDescription)
Output Inserted.ID, Inserted. ParentDescription Into @Parent
SELECT DISTINCT ParentDescription from #SourceData

INSERT INTO ChildTable (ParentID, Somedescription, someValue)
Output inserted.ID, Inserted.ParentID, Inserted.someValue into @Child
SELECT ID, ChildDescription, TheValue
FROM @Parent p inner join #SourceData s on p.Descr = s.ParentDescription

INSERT INTO AuditTable (StartParentRange, EndParentRange, StartChildRange, EndChildRange, TotalChildRows, TotalValue)
SELECT Min(ParentID), MAX(ParentID), Min(id), max(id), Count(*), SUM(SomeValue)
FROM @Child

That’s fairly nice. No triggers, no need to insert the parents 1 by 1 to get at the identity values, no need to reselect from the tables after the insert (which can be expensive if they’re large)

On SQL 2005, that’s the best that’s possible, as the output can only be inserted into a table variable, or returned as a result set.

In 2008, the output from a data modification can be used as the source for another insert statement. There are a lot of restrictions to it, so it’s not the most useful of features at the moment. Joins are not allowed so the data modification has to be the sole source for the second insert statement. Aggregations are also not allowed. The destination table must not have foreign keys. Still it will allow us to reduce the table variables by one.

With the same source data as the prior example, for SQL 2008:

DECLARE @Parent Table (id int, Descr Varchar(50))

INSERT INTO ParentTable (ParentDescription)
Output Inserted.ID, Inserted. ParentDescription Into @Parent
SELECT DISTINCT ParentDescription from #SourceData

Insert into AuditTable (ChildID, SomeValue)
SELECT id, SomeValue
FROM
(INSERT INTO ChildTable (ParentID, Somedescription, someValue)
Output inserted.ID, Inserted.someValue
SELECT ID, ChildDescription, TheValue
FROM @Parent p inner join #SourceData s on p.Descr = s.ParentDescription) AS i (id, somevalue)

With all the restrictions on the nested inserts, they’re not as useful as they seem, but it is an interesting technique, has at least a few uses and hopefully will be less restricted in future versions.