Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts

By Bill Graziano on 14 August 2006 | Tags: Identity , INSERT

SQL Server 2005 introducted the OUTPUT clause which we can use to capture values from the inserted and deleted virtual tables. Previously this data was only available through triggers. We can use this in an INSERT ... SELECT statement to capture all the inserted identity values. Previously this required some type of loop or temporarily altering the target table.

We'll start with two tables: a product table and table of products to insert. The scenario is a vendor that sends you a complete list of all their products and you only need to insert the rows that don't already exist. However you need to insert those new rows into multiple tables. The following script will create the tables in tempdb based on data in AdventureWorks.

USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects 
		WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
	DROP TABLE [dbo].[Product]
GO
IF  EXISTS (SELECT * FROM sys.objects 
		WHERE object_id = OBJECT_ID(N'[dbo].ProductsToInsert') AND type in (N'U'))
	DROP TABLE [dbo].ProductsToInsert
GO

CREATE TABLE Product (
	ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(50) NOT NULL,
	ProductNumber NVARCHAR(25) NOT NULL,
	ListPrice MONEY NOT NULL)
GO
CREATE UNIQUE INDEX IX_Product_ProductNumber ON Product ( ProductNumber )
GO

CREATE TABLE ProductsToInsert (
	RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(50) NOT NULL,
	ProductNumber NVARCHAR(25) NOT NULL,
	ListPrice MONEY NOT NULL,
	InsertedIdentityValue INT NULL)
GO
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT TOP 450 [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
ORDER BY SellStartDate, ProductID
GO
INSERT ProductsToInsert ([Name], ProductNumber, ListPrice)
SELECT  [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
GO

The Product table has an identity column as its primary key. Product number is a natural key on the table. The ProductsToInsert table has the ProductNumber column and a column for whatever identity value is inserted when we put the row into the Product table. You'll notice I only put 450 of the products in the Product table to start with but all 504 in the ProductsToInsert table.

A simple script to insert the new products looks like this:

use tempdb
GO
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT 
    [Name], ProductNumber, ListPrice
FROM
    ProductsToInsert I
WHERE
    NOT EXISTS (SELECT 1 
                FROM Product 
                WHERE ProductNumber = I.ProductNumber)

That inserts the 54 products that weren't in the table previously. We can use the OUTPUT clause to return back the rows that were inserted. That looks like this:

INSERT Product ([Name], ProductNumber, ListPrice)
    OUTPUT inserted.ProductID,
           inserted.[Name],
           inserted.ProductNumber,
           inserted.ListPrice
SELECT 
	[Name], ProductNumber, ListPrice
FROM
	ProductsToInsert I
WHERE
	NOT EXISTS (SELECT 1 FROM Product 
				WHERE ProductNumber = I.ProductNumber)

When that statement is run it returns the following recordset back to the client:

  ProductID Name                                ProductNumber         ListPrice
----------- ----------------------------------- --------------------- -------------
        451 LL Bottom Bracket                   BB-7421                       53.99
        452 ML Bottom Bracket                   BB-8107                      101.24
        453 HL Bottom Bracket                   BB-9108                      121.49

. . . 

        504 HL Touring Handlebars               HB-T928                       91.57

(54 row(s) affected)

That is almost what we want. We have the identity values in the result set but we don't have a way to work with the data and we don't have a way to tie it back to the original source row. We'll need to add two things. First we'll need to store this result set in a table variable. I'll also remove a few columns to make it easier to read and add an update statement to save the identity value. That script looks like this:

DECLARE @InsertedRows TABLE (ProductID INT, ProductNumber NVARCHAR(25) )

INSERT Product ([Name], ProductNumber, ListPrice)
    OUTPUT inserted.ProductID,
           inserted.ProductNumber
    INTO @InsertedRows
SELECT 
	[Name], ProductNumber, ListPrice
FROM
	ProductsToInsert AS I
WHERE
	NOT EXISTS (SELECT 1 FROM Product 
				WHERE ProductNumber = I.ProductNumber)

UPDATE  ProductsToInsert
SET     InsertedIdentityValue = T.ProductID
FROM    ProductsToInsert I
JOIN    @InsertedRows T ON T.ProductNumber = I.ProductNumber

SELECT  RowID, ProductNumber, InsertedIdentityValue
FROM    ProductsToInsert
WHERE   InsertedIdentityValue IS NOT NULL

We declare a table variable to store the results of the OUTPUT clause. We use the OUTPUT INTO syntax to store the results into the table variable. Next we use the table variable to update the source table with the inserted identity columns. After that a simple SELECT statement returns the new values which we can easily use in other statements:

      RowID ProductNumber             InsertedIdentityValue
----------- ------------------------- ---------------------
        451 HB-T721                                     503
        452 HB-T928                                     504
        453 FB-9873                                     502

. . . 

        504 BK-R19B-52                                  470

(54 row(s) affected)

The OUTPUT clause can also be used with UPDATE and DELETE statements and return values from either the inserted or deleted table. In its simplest form the OUTPUT clause greatly simplifies importing data into SQL Server.