SQL SERVER 2005中全新的OUTPUT子句添加数据记录详解(实战篇)

实战篇

A. 将 OUTPUT INTO 用于简单 INSERT 语句

以下示例将行插入 ScrapReason 表，并使用 OUTPUT 子句将语句的结果返回到 @MyTableVar table 变量。由于 ScrapReasonID 列使用 IDENTITY 属性定义，因此未在 INSERT 语句中为该列指定一个值。但请注意，将在列 INSERTED.ScrapReasonID 内的 OUTPUT 子句中返回由数据库引擎 为该列生成的值。

复制代码

USE AdventureWorks; GO DECLARE @MyTableVar table( ScrapReasonID smallint, Name varchar(50), ModifiedDate datetime); INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE()); --Display the result set of the table variable. SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar; --Display the result set of the table. SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason; GO

B. 将 OUTPUT 用于 INSERT…SELECT 语句

以下示例创建 EmployeeSales 表，然后通过使用 SELECT 语句检索源表中的数据将几行插入该表。同时，也计算了列 ProjectedSales 的值并将其插入该表中。OUTPUT 子句将 INSERT 语句的结果返回到执行调用的应用程序。最后的 SELECT 语句验证新 EmployeeSales 表的内容是否与 OUTPUT 子句的结果匹配。

复制代码

USE AdventureWorks ; GO IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL DROP TABLE dbo.EmployeeSales; GO CREATE TABLE dbo.EmployeeSales ( EmployeeID nvarchar(11) NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, CurrentSales money NOT NULL, ProjectedSales money NOT NULL ); GO INSERT INTO dbo.EmployeeSales OUTPUT INSERTED.EmployeeID, INSERTED.LastName, INSERTED.FirstName, INSERTED.CurrentSales, INSERTED.ProjectedSales SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10 FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS sp ON e.EmployeeID = sp.SalesPersonID INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID LIKE '2%' ORDER BY c.LastName, c.FirstName; GO SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales FROM dbo.EmployeeSales; GO

C. 将 OUTPUT 用于 DELETE 语句

以下示例将删除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* 指定 DELETE 语句的结果（即已删除的行中的所有列）返回到执行调用的应用程序。后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。

	复制代码
USE AdventureWorks; GO DELETE Sales.ShoppingCartItem OUTPUT DELETED.* ; --Verify all rows in the table have been deleted. SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem; GO

D. 将 OUTPUT INTO 用于 UPDATE

下面的示例将 Employee 表中 VacationHours 列的前 10 行更新 25%。OUTPUT 子句将返回 VacationHours 值，该值在将列 DELETED.VacationHours 中的 UPDATE 语句和列 INSERTED.VacationHours 中的已更新值应用于 @MyTableVar table 变量之前存在。

在它后面的两个 SELECT 语句返回 @MyTableVar 中的值以及 Employee 表中更新操作的结果。请注意，INSERTED.ModifiedDate 列中的结果与 Employee 表中的 ModifiedDate 列不具有相同的值。这是因为对 Employee 表定义了将 ModifiedDate 的值更新为当前日期的 AFTER UPDATE 触发器。但是，从 OUTPUT 中返回的列反映触发器激发之前的数据。

复制代码

USE AdventureWorks; GO DECLARE @MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime); UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDate INTO @MyTableVar; --Display the result set of the table variable. SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate FROM @MyTableVar; GO --Display the result set of the table. --Note that ModifiedDate reflects the value generated by an --AFTER UPDATE trigger. SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate FROM HumanResources.Employee; GO

E. 使用 OUTPUT INTO 返回表达式

以下示例在示例 D 的基础上生成，方法是通过将 OUTPUT 子句中的表达式定义为已更新的 VacationHours 值与应用更新之前的 VacationHours 值之间的差异。该表达式的值返回到列 VacationHoursDifference 中的 @MyTableVar table 变量。

复制代码

USE AdventureWorks; GO DECLARE @MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, VacationHoursDifference int, ModifiedDate datetime); UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.VacationHours - DELETED.VacationHours, INSERTED.ModifiedDate INTO @MyTableVar; --Display the result set of the table variable. SELECT EmpID, OldVacationHours, NewVacationHours, VacationHoursDifference, ModifiedDate FROM @MyTableVar; GO SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate FROM HumanResources.Employee; GO

F. 在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTO

以下示例使用指定的 ProductID 和 ScrapReasonID，针对 WorkOrder 表中的所有工作顺序更新 ScrapReasonID 列。OUTPUT INTO 子句返回所更新表 (WorkOrder) 中的值以及 Product 表中的值。在 FROM 子句中使用 Product 表来指定要更新的行。由于 WorkOrder 表具有对其定义的 AFTER UPDATE 触发器，因此需要 INTO 关键字。

复制代码

USE AdventureWorks; GO DECLARE @MyTestVar table ( OldScrapReasonID int NOT NULL, NewScrapReasonID int NOT NULL, WorkOrderID int NOT NULL, ProductID int NOT NULL, ProductName nvarchar(50)NOT NULL); UPDATE Production.WorkOrder SET ScrapReasonID = 4 OUTPUT DELETED.ScrapReasonID, INSERTED.ScrapReasonID, INSERTED.WorkOrderID, INSERTED.ProductID, p.Name INTO @MyTestVar FROM Production.WorkOrder AS wo INNER JOIN Production.Product AS p ON wo.ProductID = p.ProductID AND wo.ScrapReasonID= 16 AND p.ProductID = 733; SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID, ProductID, ProductName FROM @MyTestVar; GO

G. 在 DELETE 语句中使用包含 from_table_name 的 OUTPUT INTO

以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。OUTPUT 子句返回所删除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列。在 FROM 子句中使用该表来指定要删除的行。

复制代码

USE AdventureWorks GO DECLARE @MyTableVar table ( ProductID int NOT NULL, ProductName nvarchar(50)NOT NULL, ProductModelID int NOT NULL, PhotoID int NOT NULL); DELETE Production.ProductProductPhoto OUTPUT DELETED.ProductID, p.Name, p.ProductModelID, DELETED.ProductPhotoID INTO @MyTableVar FROM Production.ProductProductPhoto AS ph JOIN Production.Product as p ON ph.ProductID = p.ProductID WHERE p.ProductModelID BETWEEN 120 and 130; --Display the results of the table variable. SELECT ProductID, ProductName, ProductModelID, PhotoID FROM @MyTableVar ORDER BY ProductModelID; GO

H. 将 OUTPUT INTO 用于大型对象数据类型

以下示例使用 .WRITE 子句更新 Production.Document 表内 DocumentSummary 这一 nvarchar(max) 列中的部分值。通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词 components 替换为单词 features。此示例使用 OUTPUT 子句将 DocumentSummary 列的前像和后像返回到 @MyTableVar table 变量。请注意，将返回 DocumentSummary 列的全部前像和后像。

	复制代码
USE AdventureWorks; GO DECLARE @MyTableVar table ( DocumentID int NOT NULL, SummaryBefore nvarchar(max), SummaryAfter nvarchar(max)); UPDATE Production.Document SET DocumentSummary .WRITE (N'features',28,10) OUTPUT INSERTED.DocumentID, DELETED.DocumentSummary, INSERTED.DocumentSummary INTO @MyTableVar WHERE DocumentID = 3 ; SELECT DocumentID, SummaryBefore, SummaryAfter FROM @MyTableVar; GO

I. 在 INSTEAD OF 触发器中使用 OUTPUT

以下示例在触发器中使用 OUTPUT 子句返回触发器操作的结果。首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。由于列 ScrapReasonID 是基表中的 IDENTITY 列，因此触发器将忽略用户提供的值。这允许数据库引擎 自动生成正确的值。同样，用户为 ModifiedDate 提供的值也被忽略并设置为正确的日期。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

复制代码

UUSE AdventureWorks; GO IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL DROP VIEW dbo.vw_ScrapReason; GO CREATE VIEW dbo.vw_ScrapReason AS (SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason); GO CREATE TRIGGER dbo.io_ScrapReason ON dbo.vw_ScrapReason INSTEAD OF INSERT AS BEGIN --ScrapReasonID is not specified in the list of columns to be inserted --because it is an IDENTITY column. INSERT INTO Production.ScrapReason (Name, ModifiedDate) OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate SELECT Name, getdate() FROM inserted; END GO INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate) VALUES (99, N'My scrap reason','20030404'); GO

这是在 2004 年 4 月 12 日 ('2004-04-12') 生成的结果集。请注意，ScrapReasonIDActual 和 ModifiedDate 列反映由触发器操作生成的值而不是 INSERT 语句中提供的值。

	复制代码
ScrapReasonID Name ModifiedDate ------------- ---------------- ----------------------- 17 My scrap reason 2004-04-12 16:23:33.050

J. 将 OUTPUT INTO 用于标识列和计算列

下面的示例创建 EmployeeSales 表，然后使用 INSERT 语句向其中插入若干行，并使用 SELECT 语句从源表中检索数据。EmployeeSales 表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。由于这些值是在插入操作期间由数据库引擎生成的，因此，不能在 @MyTableVar 中定义上述两列。

复制代码

USE AdventureWorks ; GO IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL DROP TABLE dbo.EmployeeSales; GO CREATE TABLE dbo.EmployeeSales ( EmployeeID int IDENTITY (1,5)NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, CurrentSales money NOT NULL, ProjectedSales AS CurrentSales * 1.10 ); GO DECLARE @MyTableVar table( LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, CurrentSales money NOT NULL ); INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales) OUTPUT INSERTED.LastName, INSERTED.FirstName, INSERTED.CurrentSales INTO @MyTableVar SELECT c.LastName, c.FirstName, sp.SalesYTD FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS sp ON e.EmployeeID = sp.SalesPersonID INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID LIKE '2%' ORDER BY c.LastName, c.FirstName; SELECT LastName, FirstName, CurrentSales FROM @MyTableVar; GO SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales FROM dbo.EmployeeSales; GO

在单个语句中使用 OUTPUT 与 OUTPUT INTO

以下示例将按照在 DELETE 语句的 FROM 子句中所定义的搜索条件删除 ProductProductPhoto 表中的行。OUTPUT INTO 子句将所删除表（DELETED.ProductID、DELETED.ProductPhotoID）中的列以及 Product 表中的列返回到 @MyTableVar table 变量。在 FROM 子句中使用 Product 表来指定要删除的行。OUTPUT 子句将 ProductProductPhoto 表中的 DELETED.ProductID、DELETED.ProductPhotoID 列以及行的删除日期和时间返回到执行调用的应用程序。

复制代码

USE AdventureWorks GO DECLARE @MyTableVar table ( ProductID int NOT NULL, ProductName nvarchar(50)NOT NULL, ProductModelID int NOT NULL, PhotoID int NOT NULL); DELETE Production.ProductProductPhoto OUTPUT DELETED.ProductID, p.Name, p.ProductModelID, DELETED.ProductPhotoID INTO @MyTableVar OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate FROM Production.ProductProductPhoto AS ph JOIN Production.Product as p ON ph.ProductID = p.ProductID WHERE p.ProductID BETWEEN 800 and 810; --Display the results of the table variable. SELECT ProductID, ProductName, PhotoID, ProductModelID FROM @MyTableVar; GO