在多行插入时使用OutPut语句捕获每一行的标识

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址正在寻找之中………………

SQLServer2005引入了Output语句，能用来捕获虚拟表inserted和deleted的值。以前的版本只能通过触发器来捕获。现在可以在Insert……select语句中捕获所有标识的值了。以前要完成此功能需要使用循环或者临时改变目标表来实现。

现在从两个表开始：一个是product，一个是ProductsToInsert。假定供应商给你一个他们所有产品的表单，而你仅仅需要插入那些不存在的行。而且你需要把这些行行插入到多个表中。下来脚本基于数据库AdventureWorks的数据在tempdb里创建表：

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

Product表有一个自增列作为主键，ProductNumber列为自然键。在表ProductsToInsert中也有列ProductNumber，当向表Products中插入数据时记录标识值。注意一开始在product表中插入450行，而在ProductsToInsert表中插入所有的504行。

如下的一个简单的插入脚本将向表Product插入新的54行：

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)

我们能够使用output语句返回刚刚插入的那些行，脚本如下：

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)

运行该语句后返回结果如下：

  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)

这基本上就是我们所需要的。得到了结果集中的标识值但没法使用这些数据，而且也不能从源数据中返回。这时我们需要做两个事情。首先需
要存储这些结果集到一个表变量中（这里为了简洁少写了一些列）。脚本如下：

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

我们声明了一个表变量来存储Output语句的结果。使用OUTPUT INTO语法将结果存到表变量中。接下来使用表变量去更新源表ProductsToInsert的列InsertedIdentityValue。然后执行select语句返回新的值，结果如下：
      RowID ProductNumber            InsertedIdentityValue
----------- ------------------------- ---------------------
        451 HB-T721                                    503
        452 HB-T928                                    504
        453 FB-9873                                    502
. . .
        504 BK-R19B-52                                  470
(54 row(s) affected)

OUTPUT语句也能使用在UPDATE和DELETE语句中，用来返回inserted或者deleted表的值。OUTPUT语句这种简单的形式大大简化了SQLServer数据的导入。