在insert語句或者delete語句執(zhí)行后，SQL Server只返回受影響的記錄行的總數(shù)，如果我們想知道到底所插入/所刪除的記錄的具體信息的話，這就有點復(fù)雜了。在SQL Server 2005之前的版本中，如果希望從已插入和已刪除的虛擬表中捕獲數(shù)據(jù)，我們只能通過觸發(fā)器來實現(xiàn)。在SQL Server 2005中，我們可以利用OUTPUT子句來實現(xiàn)這個功能。我們可以在insert ... SELECT語句使用OUTPUT子句，捕獲所有插入的標(biāo)識值。以前這需要某種類型的循環(huán)或暫時改變目標(biāo)表才能實現(xiàn)。
下面通過具體例子來詳細(xì)介紹如何使用OUTPUT子句。
我們新建兩個數(shù)據(jù)表：一個product產(chǎn)品表，一個ProductsToInsert待插入的產(chǎn)品表。假設(shè)供應(yīng)商向您發(fā)送了一個完整的列出所有的產(chǎn)品，但你只需要插入之前不存在的記錄。并且你需要將這些新行插入到多個表格中。
下面的腳本將根據(jù)AdventureWorks數(shù)據(jù)庫中的記錄，在tempdb數(shù)據(jù)庫中創(chuàng)建表。
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
--創(chuàng)建Product表
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
--創(chuàng)建ProductsToInsert表
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
--向Product表插入數(shù)據(jù)
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT 450 [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
ORDER BY SellStartDate, ProductID
GO
--向ProductToInsert表插入數(shù)據(jù)
INSERT ProductsToInsert ([Name], ProductNumber, ListPrice)
SELECT [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
GO
Product表有個ID字段作為主鍵。Productnumber是Product表中一個自然鍵。 ProductsToInsert表的有ProductNumber字段以及另外記錄標(biāo)識值的字段，向產(chǎn)品表插入記錄時將會在該字段中記錄標(biāo)識值。
上面語句中，只取了450條產(chǎn)品信息作為Product數(shù)據(jù)表的初始化數(shù)據(jù)，取了全部504條記錄作為ProductsToInsert數(shù)據(jù)表的初始化數(shù)據(jù)。
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)
該語句執(zhí)行完后，在以前的表中不存在的54種產(chǎn)品將被插入到product表中。我們可以使用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)
這差不多就是我們想要的了。我們得到了結(jié)果集的標(biāo)識值，但我們沒有利用這些數(shù)據(jù)，因為我們沒法把它還原到原來的記錄值。
我們還將需要增加兩件事。首先，我們將需要儲存此結(jié)果集保存到一個表值變量中。我會刪除一些字段，使之更易于閱讀;添加一個update語句以保存標(biāo)識值。腳本如下所示：
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子句的結(jié)果。我們使用OUTPUT into這個語法將結(jié)果存儲到表變量中。
下一步我們使用表變量，根據(jù)插入的標(biāo)識列更新源表。
最后，一個簡單的SELECT語句返回新的數(shù)值，我們可以很容易地在其他語句中使用這些數(shù)值。
OUTPUT 子句對于在 INSERT操作之后檢索標(biāo)識列或計算列的值可能非常有用。另外OUTPUT子句也可以在UPDATE和DELETE語句中使用，從插入表或刪除表中得到數(shù)值，并返回這些數(shù)值?？荚嚧筇崾救绻麑?shù)或變量作為 UPDATE 語句的一部分進行了修改，則 OUTPUT 子句將始終返回語句執(zhí)行之前的參數(shù)或變量的值而不是已修改的值。
以下語句中不支持 OUTPUT 子句：
l 引用本地分區(qū)視圖、分布式分區(qū)視圖或遠程表的 DML 語句。
l 包含 EXECUTE 語句的 INSERT 語句。
l 不能將 OUTPUT INTO 子句插入視圖或行集函數(shù)。
簡潔的OUTPUT子句，使得向SQL Server導(dǎo)入數(shù)據(jù)的操作得到了極大的簡化。
下面通過具體例子來詳細(xì)介紹如何使用OUTPUT子句。
我們新建兩個數(shù)據(jù)表：一個product產(chǎn)品表，一個ProductsToInsert待插入的產(chǎn)品表。假設(shè)供應(yīng)商向您發(fā)送了一個完整的列出所有的產(chǎn)品，但你只需要插入之前不存在的記錄。并且你需要將這些新行插入到多個表格中。
下面的腳本將根據(jù)AdventureWorks數(shù)據(jù)庫中的記錄，在tempdb數(shù)據(jù)庫中創(chuàng)建表。
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
--創(chuàng)建Product表
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
--創(chuàng)建ProductsToInsert表
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
--向Product表插入數(shù)據(jù)
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT 450 [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
ORDER BY SellStartDate, ProductID
GO
--向ProductToInsert表插入數(shù)據(jù)
INSERT ProductsToInsert ([Name], ProductNumber, ListPrice)
SELECT [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
GO
Product表有個ID字段作為主鍵。Productnumber是Product表中一個自然鍵。 ProductsToInsert表的有ProductNumber字段以及另外記錄標(biāo)識值的字段，向產(chǎn)品表插入記錄時將會在該字段中記錄標(biāo)識值。
上面語句中，只取了450條產(chǎn)品信息作為Product數(shù)據(jù)表的初始化數(shù)據(jù)，取了全部504條記錄作為ProductsToInsert數(shù)據(jù)表的初始化數(shù)據(jù)。
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)
該語句執(zhí)行完后，在以前的表中不存在的54種產(chǎn)品將被插入到product表中。我們可以使用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)
這差不多就是我們想要的了。我們得到了結(jié)果集的標(biāo)識值，但我們沒有利用這些數(shù)據(jù)，因為我們沒法把它還原到原來的記錄值。
我們還將需要增加兩件事。首先，我們將需要儲存此結(jié)果集保存到一個表值變量中。我會刪除一些字段，使之更易于閱讀;添加一個update語句以保存標(biāo)識值。腳本如下所示：
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子句的結(jié)果。我們使用OUTPUT into這個語法將結(jié)果存儲到表變量中。
下一步我們使用表變量，根據(jù)插入的標(biāo)識列更新源表。
最后，一個簡單的SELECT語句返回新的數(shù)值，我們可以很容易地在其他語句中使用這些數(shù)值。
OUTPUT 子句對于在 INSERT操作之后檢索標(biāo)識列或計算列的值可能非常有用。另外OUTPUT子句也可以在UPDATE和DELETE語句中使用，從插入表或刪除表中得到數(shù)值，并返回這些數(shù)值?？荚嚧筇崾救绻麑?shù)或變量作為 UPDATE 語句的一部分進行了修改，則 OUTPUT 子句將始終返回語句執(zhí)行之前的參數(shù)或變量的值而不是已修改的值。
以下語句中不支持 OUTPUT 子句：
l 引用本地分區(qū)視圖、分布式分區(qū)視圖或遠程表的 DML 語句。
l 包含 EXECUTE 語句的 INSERT 語句。
l 不能將 OUTPUT INTO 子句插入視圖或行集函數(shù)。
簡潔的OUTPUT子句，使得向SQL Server導(dǎo)入數(shù)據(jù)的操作得到了極大的簡化。