Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts
By Bill Graziano
on 14 August 2006
| 0 Comments
| 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.
