Get Identity Values of Multi-row insert by using OUTPUT Clause

By Web Star
C# .NET
Articles

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a targe

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

--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 ProductsToInsert ([Name], ProductNumber, ListPrice)

SELECT [Name], ProductNumber, ListPrice

FROM AdventureWorks.Production.Product

GO

INSERT Product ([Name], ProductNumber, ListPrice)

SELECT

[Name], ProductNumber, ListPrice

FROM

ProductsToInsert I

WHERE

NOT EXISTS (SELECT 1

FROM Product

WHERE ProductNumber = I.ProductNumber)

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)

Popularity  (1679 Views)
View Web Star's Articles
Biography - Web Star
Web developer using Asp.net,C#.net,Sql Server, Silverlight, Javascript, CSS, AJAX etc.
Create New Account
Article Discussion: Get Identity Values of Multi-row insert by using OUTPUT Clause
Web Star posted at Tuesday, October 21, 2008 8:08 AM
Original Article
reply
eliza sahoo replied to Web Star at Tuesday, October 21, 2008 6:41 PM

Let's start with 2 tables CustomerTable and CustomerToInsertTable. The scenario is- we are getting a complete list of all the customers from a vendor from CustomerToInsertTable and we want to update CustomerTable from CustomerToInsertTable the by inserting the rows that don't already exist. However we also need to insert those new rows in other multiple tables.

STEP 1 : The  script below will create new tables.

CREATE TABLE [CustomerTable]

      (

            RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

            CustomerKey INT,

            CustomerName NVARCHAR(50) NOT NULL,

            UserName NVARCHAR(25) NOT NULL,

            Password NVARCHAR(25) NOT NULL

      )

  

CREATE TABLE CustomerToInsertTable

      (

            CustomerID INT NOT NULL IDENTITY(1,1) PRIMARYKEY,

            CustomerName NVARCHAR(50) NOT NULL,

            UserName NVARCHAR(25) NOT NULL,

            Password NVARCHAR(25) NOT NULL,

            InsertedIdentityValue INT NULL

      )

 

 STEP 2 : Now let's insert 16 new rows to the CustomerToInsertTable.

 INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES ('Windell L.', '1108', '14117')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Rad', '1114', '8927')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Charles F.', '1127', '12287')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Andrew R.', '1128', '9605')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Steven R.', '1191', '8420')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Robert', '1200', '18971')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Ronald F.', '1205', '13730')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Deidra', '1209', '9902')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Rena', '1264', '9611')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Ann L', '1290', '9548')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Robert', '1302', '9083')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Jerry H.', '1313', '14735')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password)VALUES('Joan', '1322', '10211')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password) VALUES('Mark', '1355', '10028')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password)VALUES('Terry', '1358', '8564')

INSERT INTO CustomerToInsertTable(CustomerName, UserName,Password)VALUES('Bret', '1373', '10067')

 

STEP 3 : Copy top 5 rows to CustomerTable from CustomerToInsertTable.

 

INSERT [CustomerTable](CustomerKey, CustomerName, UserName,Password)

SELECT TOP 5 CustomerID, CustomerName, UserName, Password

FROM CustomerToInsertTable ORDER BY CustomerID

 

STEP 4 :

 

So we are having 16 rows in CustomerToInsertTable and only 5 in CustomerTable. We need to insert rows in the CustomerTable by getting new rows from the CustomerToInsertTable and also save them in a temporary table, that can be used to insert or update any other tables.

Here I have updated the CustomerToInsertTable.InsertedIdentityValue with the CustomerTable.RowID.

 

--Declare Temp Table @InsertedRows

DECLARE @InsertedRows TABLE (RowID INT, CustomerKey INT,CustomerName NVARCHAR(50) )

--Insert Non existing rows in CustomerTable From CustomerToInsertTable

INSERT CustomerTable (CustomerKey, CustomerName, UserName,Password)

    OUTPUT inserted.RowID,

           inserted.CustomerKey,

           inserted.CustomerName

    INTO   @InsertedRows

SELECT  CustomerID, CustomerName, UserName,Password

FROM  CustomerToInsertTable AS CIT

WHERE NOT EXISTS (SELECT 1 FROM CustomerTable WHERECustomerKey = CIT.CustomerID)

 

--Get the new rows inserted

SELECT * FROM @InsertedRows

--Update CustomerToInsertTable

UPDATE  CustomerToInsertTable

SET     InsertedIdentityValue = IR.RowID

FROM    CustomerToInsertTable CIT

JOIN    @InsertedRows IR ON IR.CustomerKey = CIT.CustomerID
reply