OUTPUT 子句

返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息，或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外，您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果，然后将这些结果插入目标表或视图。

OUTPUT子句的基本原理

1、返回由每个INSERT、UPDATE或DELETE命令所影响的记录行。OUTPUT子句可以在客户端应用程序中返回这些记录行，然后将它们插入到一个持久的或临时的表中，也可以将记录插入到一个表变量中，或直接返回到处理应用程序，也就是用于输出。
2、OUTPUT子句的用法就是直接将OUTPUT子句附到任何一个INSERT/UPDATE/DELETE语句后。
3、OUTPUT子句中可以引用INSERTED或DELETED虚拟表，这取决于是否想要在数据修改前(DELETED表)或修改后(INSERTED表)得到数据，这跟使用触发器去修改数据的操作是很相似的。
4、不能在一个INSERT语句中引用DELETED，也不能在一个DELETED语句中引用INSERTED，因为这些虚拟表在这两种情况下逻辑上是没有意义的，所以SQL Server不会去创建。
5、对于具有 OUTPUT 子句的 UPDATE、INSERT 或 DELETE 语句，即使在遇到错误需要回滚时，也会将行返回到客户端。 如果在运行语句的过程中出现任何错误，都不应使用该结果。

语法

<OUTPUT_CLAUSE> ::=
{
  [OUTPUT <dml_select_list> INTO {@table_variable|output_table} [(column_list)]]
  [OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
    [ ,...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action
  • 使用OUTPUT INTO子句的话，是把返回的结果插入到表变量(@table_variable)或表(output_table)中。
  • 使用OUTPUT子句的话，就是直接把结果直接返回到处理应用程序。
  • @table_variable必须先声明才能插入、 更新、 DELETE 或 MERGE 语句。
  • output_table指定一个表，返回的行将被插入该表中而不是返回到调用方。output_table可能是临时表。
      output_table不能︰
  • 具有启用的对其定义的触发器。
  • 参与 FOREIGN KEY 约束的任意一方。
  • 具有 CHECK 约束或启用的规则。
  • column_list
      INTO 子句目标表(上面的@table_variable | output_table)上列名的可选列表。如果column_list未指定，表变量/表必须具有和输出结果集相同的列数。标识列和计算列除外，这两种列必须跳过。
  • scalar_expression：可取计算结果为单个值的任何符号和运算符的组合。
  • column_alias_identifier：列别名。指定受删除、插入或更新操作影响的所有列都将按照它们在表中的顺序返回。
  • 在OUTPUT子句中,可以引用特殊表INSERTED和DELETED，这与在触发器中使用临时表inserted和deleted非常相似。但是OUTPUT引用的INSERTED、DELETED和触发器产生的是不一样的。
  • DELETED：指定由更新或删除操作删除的值的列前缀。 以 DELETED 为前缀的列反映了 UPDATE、DELETE 或 MERGE 语句完成之前的值。
  • INSERTED：列的前缀，指定由插入操作或更新操作添加的值。 以 INSERTED 为前缀的列反映了在 UPDATE、INSERT 或 MERGE 语句完成之后但在触发器执行之前的值。
  • from_table_name：是一个列前缀，还可以把FROM 子句中包含的表的值OUTPUT INTO到表或表变量中。如：
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;
  • $action：仅可用于 MERGE 语句。在 MERGE 语句的 OUTPUT 子句中指定一个 nvarchar(10) 类型的列，该子句为每行返回以下三个值一：'INSERT'、'UPDATE' 或 'DELETE'，返回哪个值取决于对该行执行的操作。

不支持 OUTPUT 子句

1、引用本地分区视图、分布式分区视图或远程表的 DML 语句。
2、包含 EXECUTE 语句的 INSERT 语句。
3、当数据库兼容级别设为 100 时，不允许在 OUTPUT 子句中使用全文谓词。
4、不能将 OUTPUT INTO 子句插入视图或行集函数。
5、如果用户定义的函数包含一个以表为目标的 OUTPUT INTO 子句，则不能创建该函数。

将从 OUTPUT 子句返回的数据插入表

在捕获嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果并将这些结果插入目标表时，请牢记以下信息：

  • 整个操作是原子的。INSERT 语句和包含 OUTPUT 子句的嵌套 DML 语句要么都执行，要么整个语句都失败。
  • 以下限制适用于外层 INSERT 语句的目标：
  • 目标不能为远程表、视图或公用表表达式。
  • 目标不能有 FOREIGN KEY 约束，或者被 FOREIGN KEY 约束所引用。
  • 不能对目标定义触发器。
  • 目标不能参与合并复制或事务复制的可更新订阅。
  • 在包含 <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO 子句，只能使用OUTPUT语句。如：
 INSERT INTO Production.ScrapReason (Name, ModifiedDate)
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, 
               INSERTED.ModifiedDate
    SELECT Name, getdate()
    FROM INSERTED;
  • @@ROWCOUNT 返回仅由外层 INSERT 语句插入的行。
  • 在 <dml_table_source> 子句中，SELECT 和 WHERE 子句不能包括子查询、聚合函数、排名函数、全文谓词、执行数据访问的用户定义函数或是 TEXTPTR 函数

使用包含 OUTPUT 的 DML 触发器

从 OUTPUT 中返回的列反映** INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。
  对于 INSTEAD OF 触发器，即使没有因为触发器的操作而发生修改，也会如同实际执行 INSERT、UPDATE 或 DELETE 那样生成返回的结果。如果在触发器的主体内使用包含 OUTPUT 子句的语句，则必须使用表别名来引用触发器 inserted 和 deleted 表，以免使用与OUTPUT 关联的 INSERTED 和 DELETED 表复制列引用**。
  如果指定了 OUTPUT 子句但未同时指定 INTO 关键字，则对于给定的 DML 操作，DML 操作的目标不能启用对其定义的任何触发器。例如，如果在 UPDATE 语句中定义了 OUTPUT 子句，则目标表不能具有任何启用的 UPDATE 触发器。
  如果设置了 sp_configure 选项 disallow results from triggers，则从触发器内调用语句时，不带 INTO 子句的 OUTPUT 子句将导致该语句失败。

队列

SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。应用程序负责包括可保证所需语义的适当 WHERE 子句，或者理解当针对 DML 操作可能限定多行时，没有保证的顺序。以下示例使用子查询，并假定 DatabaseLogID 列具有唯一性特征才能实现所需的排序语义。

USE tempdb;
GO

CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
)
go

INSERT INTO dbo.table1 VALUES 
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice');
GO

DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete' 
SELECT * FROM dbo.table1;

DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;

PRINT 'table1, after delete'
SELECT * FROM dbo.table1;

PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results
--table1, before delete
--id          employee
------------- ------------------------------
--1           Fred
--2           Tom
--3           Sally
--4           Alice
--
--table1, after delete
--id          employee
------------- ------------------------------
--1           Fred
--3           Sally
--@MyTableVar, after delete
--id          employee
------------- ------------------------------
--2           Tom
--4           Alice

【示例】
A、使用 OUTPUT INTO 返回表达式

USE AdventureWorks2008R2;
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,
    ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       -- scalar_expression
       inserted.VacationHours - deleted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;

B、使用包含 from_table_name 的 OUTPUT INTO

USE AdventureWorks2008R2;
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;

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

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table (
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';

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

USE AdventureWorks2008R2 ;
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 Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO

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

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
-- 用于将被删除的列返回给@MyTableVartable变量
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;
GO

F、插入从 OUTPUT 子句返回的数据
  下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将这些数据插入另一个表。

CREATE TABLE table1  (id INT,  employee VARCHAR(32));  
CREATE TABLE table2(id INT,person VARCHAR(32));  
GO
INSERT INTO table1 
SELECT a.id,a.person FROM (
   INSERT table2 
     OUTPUT INSERTED.id,INSERTED.person
   VALUES(1,'Ada')
) AS a
GO

G、在 INSTEAD OF 触发器中使用 OUTPUT
  下例在触发器中使用 OUTPUT 子句来返回触发器操作的结果。首先，创建一个 ScrapReason 表的视图，然后对该视图定义 INSTEAD OF INSERT 触发器，从而使用户只修改基表的 Name 列。OUTPUT 子句返回实际插入 ScrapReason 表中的值。

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
   -- inserted表是触发器执行的时候临时产生的
   -- INSERTED表是DML操作(这里为INSERT)完成之后产生的
    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
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 200,667评论 5赞 472
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,361评论 2赞 377
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 147,700评论 0赞 333
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,027评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,988评论 5赞 361
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,230评论 1赞 277
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,705评论 3赞 393
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,366评论 0赞 255
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,496评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,405评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,453评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,126评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,725评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,803评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,015评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,514评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,111评论 2赞 341

推荐阅读更多精彩内容

  • SQL Server向表中插入数据
    使用 INSERT 和 VALUES 插入行 INSERT 语句可向表中添加一个或多个新行。在简化处理中，INSE...
    不知名的蛋挞阅读 8,003评论 0赞 2
  • PLSQL编程详解
    1.1 基本结构 PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。 1.2 命名规则 1.3 记...
    慢清尘阅读 3,813评论 3赞 14
  • 技术 | SQL语句大全
    语 句 功 能 数据操作 SELECT——从数据库表中检索数据行和列INSERT——向数据库表添加新数据行DELE...
    戰敭阅读 5,059评论 0赞 53
  • MarkDown
    1. 简介 1.1 什么是 MyBatis ？ MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的...
    笨鸟慢飞阅读 5,422评论 0赞 4
  • 2017-08-23
    她很独寂，总会漫无目的的寻觅。 她远望天际，听见了宇宙外的另一种声音。 她想去远方，去流浪，去想像； 却害怕路上布...
    阿米的猫阅读 105评论 0赞 0