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

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)

By Web Star   Popularity  (3368 Views)