Starting with SQL 2005 SQL introduced a new clause for your INSERT, UPDATE, DELETE, and MERGE statements…the OUTPUT clause. If you’re not using it yet, it might be because you’re not sure what you could use it for. Let’s go through a scenario where the OUTPUT clause could save you from doing a RBAR (Row By Agonizing Row) operation.

INSERT a record and retrieve the IDENTITY created

I’m going to insert a record into AdventureWorks.Sales.SalesOrderDetail, and return the SalesOrderDetailID created by the INSERT.

INSERT INTO Sales.SalesOrderDetail
(
      SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
    , rowguid, ModifiedDate
)
SELECT TOP 1
      43659 AS SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
    , NEWID() AS rowguid, GETDATE() AS ModifiedDate
FROM Sales.SalesOrderDetail
WHERE
      SalesOrderID = 43660;
  
SELECT SCOPE_IDENTITY() AS CreatedIdentity;
  

Simple enough right?

What if I wanted to INSERT more than one record?

Well, if you only had access to SCOPE_IDENTITY() then you’d have to run row by row, inserting one at a time, and selecting the identities created. If you’re doing one or two records that might be fine. But as SQL developers you have to think in sets of thousands.

Considering INSERT statements with thousands of rows, you need to learn how the OUTPUT clause could show you all the identities created. Let’s use OUTPUT now.

SELECT
COUNT(*)
FROM Sales.SalesOrderDetail
WHERE
      SalesOrderID = 43660
--2 rows    
  

We’re going to insert two rows into Sales.SalesOrder Detail.

INSERT INTO sales.salesorderdetail
(
      SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
    , rowguid, ModifiedDate
)
OUTPUT
      inserted.SalesOrderDetailID
SELECT
      43659 as SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, NEWID() as rowguid, GETDATE() as ModifiedDate
FROM Sales.SalesOrderDetail
WHERE
      SalesOrderID = 43660;

The syntax for OUTPUT is pretty simple, you just add the clause between the INSERT INTO and the SELECT or VALUES clause. Then when you run it you get the identities inserted, right?

Msg 334, Level 16, State 1, Line 1
The target table 'sales.salesorderdetail' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Close… But no cigar. This is the one catch with the OUTPUT clause. It’s not an option if your table already has a trigger. You could get away with an OUTPUT INTO clause, we’ll cover that option later. For now, I’ll just remind you you can DISABLE a TRIGGER.

DISABLE TRIGGER iduSalesOrderDetail ON sales.salesorderdetail 
GO 
INSERT INTO sales.salesorderdetail 
( 
      SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount 
, rowguid, ModifiedDate 
) 
OUTPUT 
      inserted.SalesOrderDetailID 
SELECT 
      43659 as SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount 
, NEWID() as rowguid, GETDATE() as ModifiedDate 
FROM Sales.SalesOrderDetail 
WHERE 
      SalesOrderID = 43660 
GO 
ENABLE TRIGGER iduSalesOrderDetail  ON sales.salesorderdetail 
GO 
  

And now you get the two identities you created with this insert statement.

That’s just the beginning.

Over the past few weeks, I’ve worked with developers to build queue tables and help them process records in that table. One process adds records to the queue. Another process picks up a couple thousand at a time, then does work based on that set. Before we were getting lots of locking and blocking and quite a few deadlocks.

But with some creative indexing and OUTPUT clauses, we were able to eliminate all the contention between the process that adds records to the queue, and the process that removes records from the queue.

I’ve also built archival processes using the OUTPUT clause. When it deletes from a table, the OUTPUT INTO clause puts those records into an archival table.

The OUTPUT INTO clause?

Yup. It’s just like the OUTPUT clause, except it puts the data into a table. The table can be a real table, or a table variable. Let’s take the output example from above, and shove it into a table variable called @InsertedIdentities.

GO 
DISABLE TRIGGER iduSalesOrderDetail ON sales.salesorderdetail 
GO 
  
DECLARE @InsertedIdentities TABLE ( 
      SalesOrderDetailID INT 
); 
  
INSERT INTO sales.salesorderdetail 
( 
      SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount 
    , rowguid, ModifiedDate 
) 
OUTPUT 
      inserted.SalesOrderDetailID 
INTO @InsertedIdentities 
SELECT 
      43659 as SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount 
    , NEWID() as rowguid, GETDATE() as ModifiedDate 
FROM Sales.SalesOrderDetail 
WHERE 
      SalesOrderID = 43660; 
  
SELECT * 
FROM @InsertedIdentities; 
GO 
ENABLE TRIGGER iduSalesOrderDetail  ON sales.salesorderdetail 
GO 
  

Note that the INTO part of the OUTPUT INTO clause comes after the list of columns you’re trying to INSERT INTO the destination table.

Conclusion

Get to know the OUTPUT clause, it’s incredibly useful, especially when doing a scan of a table could result in reading through millions of rows to find out which ones you just updated. You can get the data out of the subset of rows you updated. A serious performance gain when you start talking VLDBs.

As always, if you have any questions, send them in…I’m here to help!

SQL201–The OUTPUT Clause

INSERT a record and retrieve the IDENTITY created

What if I wanted to INSERT more than one record?

That’s just the beginning.

Conclusion

Leave a Reply Cancel reply