Sql server中output用法解析
一、关于output子句
SQL Server 2005中的output子句，可以使你从修改语句（INSERT、UPDATE、DELETE）中将数据返回到表变量中。带结果的 DML 的有用方案包括清除和存档、消息处理应用程序以及其他方案。这一新的 OUTPUT 子句的语法为：
OUTPUTINTO @table_variable
可以通过引用插入的表和删除的表来访问被修改的行的旧/新映像，其方式与访问触发器类似。在 INSERT 语句中，只能访问插入的表。在 DELETE 语句中，只能访问删除的表。在 UPDATE 语句中，可以访问插入的表和删除的表。
二、实例说明
1、将 OUTPUT INTO 用于简单 INSERT 语句
以下示例将行插入 ScrapReason 表，并使用 OUTPUT 子句将语句的结果返回到 @MyTableVar table 变量。由于 ScrapReasonID 列使用 IDENTITY 属性定义，因此未在 INSERT 语句中为该列指定一个值。但请注意，将在列 INSERTED.ScrapReasonID 内的 OUTPUT 子句中返回由数据库引擎 为该列生成的值。
代码use AdventureWorks
go
--定义一个表格变量
declare @mytablevar table( ScrapReasonIDsmallint,
Name1varchar(50),
ModifiedDatedatetime)
insert into Production.ScrapReason
output inserted.ScrapReasonID,inserted.[Name],inserted.ModifiedDateinto @mytablevar
values ('operator error',getdate());
--显示@mytablevar中的数据
select * from @mytablevar
--显示Production.ScrapReason表中的数据
select * from Production.ScrapReason
go
以上语句中inserted是一个临时表，当我们往数据表中插入一条数据的时候数据库会创建一
个临时表inserted保存插入的记录；当我们删除某条记录的时候，数据库会创建一个临时表deleted保存删除的记录。以上语句把新插入的数据填充到表变量@mytablevar中，然后输出数据，可以看到@mytablevar中的记录和Production.ScrapReason中新插入的数据是一样的。
2、 将 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
(
EmployeeIDnvarchar(11)not null,
LastNamenvarchar(20)not null,
FirstNamenvarchar(20)not null,
CurrentSalesmoney not null,
ProjectedSalesmoney 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.EmployeeAS e
INNER JOIN Sales.SalesPersonAS sp
ON e.EmployeeID= sp.SalesPersonID
INNER JOIN Person.ContactAS c
ON e.ContactID= c.ContactID
WHERE e.EmployeeIDLIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
3、 将 OUTPUT 用于 DELETE 语句
以下示例将删除 ShoppingCartItem 表中的所有行。子句 OUTPUT DELETED.* 指定 DELETE 语句的结果（即已删除的行中的所有列）返回到执行调用的应用程序。后面的 SELECT 语句验证对 ShoppingCartItem 表所执行的删除操作的结果。

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;

--验证表中所有数据都被删除
SELECT COUNT(*)AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO
4、 将 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(
EmpIDint NOT NULL,
OldVacationHoursint,
NewVacationHoursint,
ModifiedDatedatetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours= VacationHours* 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--显示@MyTableVar的值
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--显示插入表的值
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
5、 使用 OUTPUT INTO 返回表达式
以下示例在示例 D 的基础上生成，方法是通过将 OUTPUT 子句中的表达式定义为已更新的 VacationHours 值与应用更新之前的 VacationHours 值之间的差异。该表达式的值返回到列 VacationHoursDifference 中的 @MyTableVar table 变量。
代码USE AdventureWorks;
GO
DECLARE @MyTableVar table(
sql中delete用法 EmpIDint NOT NULL,
OldVacationHoursint,
NewVacationHoursint,
VacationHoursDifferenceint,
ModifiedDatedatetime);
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;
--显示表变量中的数据
SELECT EmpID, OldVacationHours, NewVacationHours,
VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
6、 在 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 (
OldScrapReasonIDint NOT NULL,
NewScrapReasonIDint NOT NULL,
WorkOrderIDint NOT NULL,
ProductIDint NOT NULL,
ProductNamenvarchar(50)NOT NULL);
UPDATE Production.WorkOrder
SET ScrapReasonID= 4
OUTPUT DELETED.ScrapReasonID,
INSERTED.ScrapReasonID,
INSERTED.WorkOrderID,
INSERTED.ProductID,
p.Name
INTO @MyTestVar
FROM Production.WorkOrderAS wo
INNER JOIN Production.ProductAS p
ON wo.ProductID= p.ProductID
AND wo.ScrapReasonID= 16
AND p.ProductID= 733;

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
ProductID, ProductName
FROM @MyTestVar;
GO

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。