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.

7 Comments

  1. Craig Nicholson

    I noticed this functionality only with 2008 as well and I was intrigued by it. However I haven’t found a practical use for it beyond the example you provided – auditing.

    I have been thinking of using it to replace the usual multitude of CRUD stored procedures with a simple XML based ones that deal directly with the master/detail records. I think the benefits of having a single database server call to a stored procedure with an entire “entity” record represented as XML could be quite beneficial in terms of locking and the cost of using XML could be outweighed by the performance increase.

    In this case it would be a great means to return a result set containing all the identities, default and calculated values on insertion and update.

    What do you think of that idea?

    Reply
  2. Gail

    Yeah. It’s one of those features I want to like, but it’s just too limited to really love it.

    As for the XML, do you mean that the system would then have one insert, one update and one delete procedure that accepts a piece of XML and can do inserts to any table?

    Reply
  3. Hesham A. Amin

    Nice post.
    I think what Craig Nicholson means is that instead of calling a stored procedure multiple times to insert a collection of objects. The stored procedure can be designed to accept XML which the application passes to the SP by serializing the collection into XML and return the identity values using OUTPUT. Thus, reducing server round-trips.

    Reply
  4. Campey

    What works on SQL Server 2005 is:

    UPDATE Payment
    SET
    balance = balance + @amount
    OUTPUT
    INSERTED.payment_id, … INSERTED.balance, …
    @historyMessageGuid, @historySystemDateTime, GETDATE(), ‘UPDATE’
    INTO Payment_History
    (
    payment_id, … balance, …
    history_message_guid, history_system_date_time, history_real_date_time, history_action
    )
    OUTPUT
    INSERTED.payment_id, … INSERTED.balance, …
    WHERE
    Payment.payment_id = @paymentId

    This is for a single update, it would work for multiples too.

    It inserts into the history table and returns the updated values.

    Reply
  5. Benjamine Eidelman

    Hi!
    I’m reading your example, and I see that you used the ‘ParentDescription’ field to match parent and children, that’s pretty much a show-stopper for many cases.
    I think the problem that forced you to do that, is that OUTPUT clause doesn’t provide a method to match inserted vs. original ids.

    I have in a parents and children temp tables negative ids to match parent and children, then I would want to insert parents, and obtain thru the OUTPUT clause a table matching original negative id and inserted id, so I can update the children table with the new parent ids.

    But (here comes the big problem) OUTPUT clause doesn’t allow output fields from the source table when inserting!!

    i.e this is not valid:

    DECLARE @Ids TABLE ( newID in, tempID int )

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

    (#SourceData cannot be used in OUTPUT clause when inserting)

    please let me know if I explaind myself,
    any ideas?

    Reply
  6. Gail

    I wouldn’t say it’s a show-stopper. There should always be a natural key in a table, even if the Primary Key’s on an identity. If there isn’t then there’s something wrong with the table design (rows that can be identical except for the identity value)

    You’re right that you can’t reference columns from a source table in the OUTPUT. What’s available in the OUTPUT clause is exactly what would be available in a trigger

    Reply
  7. John

Leave a Comment

Your email address will not be published. Required fields are marked *