sql-server - 输出子句解释

Question

请向我解释一下 SQL Server 输出子句。

Answer 1

一些例子有帮助吗？下面都将结果输出到客户端，但您也可以OUTPUT INTO一个@table_variable(或某些条件下的标准表)

create table T
(
id int identity(1,1),
c char(1)
)

insert into T(c) 
OUTPUT inserted.* /*Output Inserted Rows - shows the ids that were allocated*/
values ('A'),('B'),('C')

返回

id          c
----------- ----
1           A
2           B
3           C

.

UPDATE T
SET c = CHAR(ASCII(c)+1)
 /*Output before and after versions of each row*/
OUTPUT deleted.*, inserted.*
WHERE id IN (2,3)

返回

id          c    id          c
----------- ---- ----------- ----
2           B    2           C
3           C    3           D

.

DELETE 
FROM T
 /*Output the row(s) that were deleted*/
OUTPUT deleted.*
WHERE DATEPART(second, getdate())%id = 0

返回(例如)

id          c
----------- ----
1           A

编辑:

作为对评论的回应，一些示例展示了如何使用 OUTPUT 插入到表中。

CREATE TABLE #T2
(
id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
c CHAR(1)
)

DECLARE @inserted TABLE
(
id UNIQUEIDENTIFIER,
c CHAR(1)
)

INSERT INTO #T2(c) 
OUTPUT inserted.id, inserted.c 
INTO @inserted
VALUES ('A')

如果您使用的是 SQL Server 2008，则可以 use composable DML

INSERT INTO @inserted
SELECT I.id, I.c
FROM 
    (
    INSERT INTO #T2(c) 
    OUTPUT inserted.id, inserted.c 
    VALUES ('B'),('C')
    ) AS I
WHERE c <> 'C' --Only add rows of interest to @inserted table

但是如果插入目标参与 PK/FK 关系，您将收到一条错误消息。 If you encounter this problem you can also use this pattern .

INSERT INTO @inserted
EXEC sp_executesql N'
    INSERT INTO #T2(c) 
    OUTPUT inserted.id, inserted.c 
    VALUES (''D''),(''E'') '