OutPut子句的使用限制

Output子句很方便,多数情况下可以省略了更新后插入或者删除后插入操作表,将2个语句变成1个语句操作。不管从语句美观还是效率上都是有不错的提升,

Output子句日常灰常有用,而且用的地方也挺多,但是确好多时候被我们忽视,今天我就也简单扫盲一下这个语句的用法。

但是对于Output自身,也是有一些限制的。

Output子句

从文档上看。主要有以下三点需要注意

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

1 Output into 的对象不能含有触发器

下面做下这4种类型的output
用法

2 Output into 的对象不能是有外键的任何一方

先搞个测试表

3 Output into 的对象不可以带有Check约束或者启用的规则

CREATE TABLE [dbo].[AAA1]
(
[ID] [int] PRIMARY KEY,
[Col2] VARCHAR(100)
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[AAA2]
(
[ID] [int] PRIMARY KEY,
[Col2] VARCHAR(100)
) ON [PRIMARY]
GO

 

1、
insert 

带有这3种条件的对象(不管是表,还是临时表,还是表变量)都不能成为Output
Into 的目标对象。

INSERT INTO dbo.AAA1
        ( ID, Col2 )
OUTPUT Inserted.ID,Inserted.Col2 INTO AAA2(ID,Col2)
VALUES  ( 4,'1' )

所以,假如在开发过程中遇到output into
和以上3个条件之前的不兼容,那要不就考虑看下能否从逻辑上去控制数据完整性,而非通过系统的这些功能。

这样就可以在插入AAA1 的同时将插入的结果输出插入到 AAA2 里面。

要不就放弃使用 output 子句。使用其他方法来改了

我想到有2个常用的场景

 

1、有些功能想要写入记录的时候也同时插入一个记录表来记录操作,很多时候会想起触发器。如果只是如此单纯的操作,那么真还不如使用一句output来得实惠了。但是这个也看具体场景,不扯太远。

如果说得不准确,请各位轻拍

2、当我们单条插入的时候,要捕获ID的话还可以使用  SCOPE_IDENTITY()
来获取,但是如果批量的时候,要获取插入的自增列对应的列,就可以使用OutPut
来捕获了~

 

2、update 

UPDATE AAA1 SET col2 = 'BB'
    OUTPUT Deleted.ID,Deleted.Col2,Inserted.ID,Inserted.Col2
    WHERE ID = 1

在update 里面呢,就会存在有 Deleted 和 inserted 2个临时表,这个就类似于
触发器里面的 deleted表和 inserted 表了。可以捕捉到更新前后的值

 

3、deleted 

DELETE FROM dbo.AAA1
    OUTPUT Deleted.ID,Deleted.Col2
    WHERE ID = 1

delete 也就是差不多,语法是一样的。

 

4、 Merge 

MERGE dbo.AAA1 AS TAR
USING (SELECT 1,'a'
) AS SOUR(ID,Col2)
ON 1 = 0
WHEN NOT MATCHED THEN INSERT (ID,Col2) VALUES (SOUR.ID,SOUR.Col2)
OUTPUT $action,Deleted.*,Inserted.*;

Merge就有一个独特一点的 $Action 的东东,这个的值会表示它的行动,有
‘INSERT’,’UPDATE’,’DELETE’
3种动作。对于要捕捉在Merge里面的变化就很好用啦~

Merge还有一点比较好用,可以把没有插入到目标的列,也带到Output里面来~这个就可以清晰的看到每一行数据的对应情况了

 

但是Output虽好,但是还是会有一些限制,有些我遇到过,有些还没测试过。(以下内容出自联机文档)

  • 整个操作是原子的。 INSERT
    语句和包含 OUTPUT 子句的嵌套 DML
    语句要么都执行,要么整个语句都失败。

  • 以下限制适用于外层
    INSERT 语句的目标:

    • 目标不能为远程表、视图或公用表表达式。
      (这个好理解,这个是指insert 的对象,并非指 output into
      的对象)

    • 目标不能有
      FOREIGN KEY 约束,或者被 FOREIGN KEY 约束所引用。 (就是output
      into 的目标表不能带外键)

    • 不能对目标定义触发器。

    • 目标不能参与合并复制或事务复制的可更新订阅。

  • 对于嵌套的
    DML 语句有以下限制:

    • 目标不能为远程表或分区视图。

    • 源本身不能包含
      <dml_table_source> 子句。

  • 在包含
    <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO
    子句。

  • @@ROWCOUNT
    返回仅由外层 INSERT 语句插入的行。

  • @@IDENTITY、SCOPE_IDENTITY
    和 IDENT_CURRENT 仅返回由嵌套的 DML
    语句生成的标识值,而不返回由外层 INSERT 语句生成的标识值。

  • 查询通知将语句作为单个实体进行处理,并且即使重大更改是来自外层
    INSERT 语句本身,所创建的任何消息的类型也将是嵌套 DML
    的类型。


  • <dml_table_source> 子句中,SELECT 和 WHERE
    子句不能包括子查询、聚合函数、排名函数、全文谓词、执行数据访问的用户定义函数或是
    TEXTPTR 函数。

 

其它东西~遇到了在继续补充