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) |
|
Didn't Find The Answer You Were Looking For? |
| EggHeadCafe has experts online right now that may know the answer to your question. We pay them a bonus for answering as many questions as they can. So, why not help them and yourself by becoming a member (free) and ask them your question right now? |
| Ask Question In Live Forum |
|
| If you have an OpenID and do not want to become a member of the EggHeadCafe forum, you can also sign on to Chat Chaos and post your question to our real time Silverlight chat application. |
| Ask Question In Chat Chaos |
|
| 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 |
 |
| |
|