Most of you are aware that Sql Server 2005 introduced the OUTPUT clause, which provided functionality to stream records affected by a write-based statement (i.e. insert/update/delete) into a table variable, which you could then use for other purposes (perhaps to log, or to archive data from a non-partitioned table in a single statement vs. a read/insert/delete operation). This was a great addition to the server, however one thing you didn't have the ability to do was directly consume the OUTPUT row-set with an outer DML statement (for example, consuming the result set produced by the OUTPUT with a select statement wrapped around the write-based statement). This however is changing with Sql 2008, as you will now be able to consume the OUTPUT result-set directly with a wrapped statement.

So, for example, this simple OUTPUT example works on Sql 2005 today, whereby the affected records of the insert statement are OUTPUT to the display/client:

use tempdb;

-- Table to output records into...
if object_id('tempdb..#testingOutput') > 0
    drop table #testingOutput;
create table #testingOutput (id int, modified_date datetime default getdate());
-- Table to test DML on...
if object_id('tempdb..#testingDml') > 0
    drop table #testingDml;
create table #testingDml (id int);

-- Perform a simple insert with output to the screen/user...
insert    #testingDml (id)
output    inserted.id
select    top 10 a.object_id
from    sys.objects a
order by a.object_id;

Additionally, the following example works as well, capturing the OUTPUT result-set into a table variable that can be re-used for a select statement following the initial DELETE statement:

-- OUTPUT capture...
-- Delete all the data, capturing the affected records into the table variable...
delete    #testingDml
output    deleted.id
into    #testingOutput (id);
-- Read the data back from the table variable...
select    id, modified_date
from    #testingOutput;

However, the following statement is not allowed in Sql 2005, where an attempt is made to directly consume the OUTPUT record-set by an outer INSERT...SELECT statement:

-- This won't work in Sql 2005 (direct consumption of the OUTPUT result-set)
insert    #testingOutput (id, modified_date)
select    id, getdate()
from    (    -- NOTICE the embedded INSERT statement here...
            insert    #testingDml (id)
            output    inserted.id
            select    a.object_id
            from    sys.objects a
            except    -- Don't get id's that already exist in the output data...
            select    o.id
            from    #testingOutput o
        ) output_data;

If you take this same script and run it against the latest Sql 2008 CTP, you'll notice it works perfectly. There are some restrictions on the usage however, some of which include:

• The nested write-based statement is only allowed when the consuming outer-select statement is the immediate source for an outer INSERT statement
• The outer SELECT statement can't include a JOIN, EXCEPT, UNION, INTERSECT, GROUP BY, etc. type statement in correlation with the nested write statement
• The outer SELECT statement CAN include a simple where clause, but nothing like a 'where not exists (subquery here)', or any other where clause that contains a sub-query (like an IN clause for example with a nested select)

You may be wondering where this would be useful, and a BIG place that it will become useful is when used in conjunction with another new feature in Sql 2008 - the MERGE statement. The MERGE in Sql 2008 is another write-based statement (in addition to the existing insert, update, delete statements), and also includes other interesting enhancements that can be used in conjunction with this new functionality for scenarios like a data-warehouse with a slowly changing dimension. Yes, I'll be posting another new post shortly that will include a breakdown of the new MERGE functionality, including some advanced usages for just these types of scenarios.

Enjoy!

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.