OUTPUT 子句 (Transact-SQL)

發行項
12/15/2008

更新： 2006 年 12 月 12 日

從 INSERT、UPDATE 或 DELETE 陳述式所影響的每個資料列傳回資訊，或傳回以 INSERT、UPDATE 或 DELETE 陳述式所影響的每個資料列為基礎的運算式。這些結果可以傳回給負責處理的應用程式，以便用在確認訊息、封存或其他這類應用程式需求等用途上。另外，這些結果也可以插入資料表或資料表變數中。

使用於：

DELETE

INSERT

UPDATE

主題連結圖示 Transact-SQL 語法慣例

語法

<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 }

引數

@table_variable
指定傳回的資料列並非傳回給呼叫者，而是插入其中的 table 變數。必須在 INSERT、UPDATE 或 DELETE 陳述式之前宣告 @table_variable。

如果未指定 column_list，table 變數必須有與 OUTPUT 結果集數目相同的資料行。識別和計算資料行例外，它們必須被略過。如果指定 column_list，任何省略的資料行都必須允許 Null 值或已指派預設值。

如需有關 table 變數的詳細資訊，請參閱＜資料表 (Transact-SQL)＞。

output_table
指定傳回的資料列並非傳回給呼叫者，而是插入其中的資料表。output_table 可能是暫存資料表。

如果未指定 column_list，資料表必須有與 OUTPUT 結果集數目相同的資料行。識別和計算資料行例外。它們必須被略過。如果指定 column_list，任何省略的資料行都必須允許 Null 值或已指派預設值。

output_table 不得：
- 啟用它所定義的觸發程序。
- 參與外部索引鍵條件約束的任何一端。
- 有 CHECK 條件約束或啟用的規則。

column_list
這是一份 INTO 子句目標資料表的資料行名稱選用清單。它類似於 INSERT 陳述式中所允許使用的資料行清單。

scalar_expression
這是會評估得出單一值的符號和運算子的任何組合。scalar_expression 中不允許使用彙總函數。

任何指向修改的資料表中之資料行的參考，都必須用 INSERTED 或 DELETED 前置詞來限定。

column_alias_identifier
這是用來參考資料行名稱的替代名稱。

DELETED
這是一個資料行前置詞，用來指定更新或刪除作業所刪除的值。前置詞是 DELETED 的資料行反映 UPDATE 或 DELETE 陳述式完成之前的值。

DELETED 不能用來搭配使用 INSERT 陳述式中的 OUTPUT 子句。

INSERTED
這是一個資料行前置詞，用來指定插入或更新作業所加入的值。前置詞是 INSERTED 的資料行反映 UPDATE 或 INSERT 陳述式完成之後、觸發程序執行之前的值。

INSERTED 不能用來搭配使用 DELETE 陳述式中的 OUTPUT 子句。

from_table_name
這是一個資料行前置詞，用來指定在指定要更新或刪除的資料列時所用的 DELETE 或 UPDATE 陳述式之 FROM 子句所包括的資料表。

如果 FROM 子句也指定了所修改的資料表，任何指向這份資料表中之資料行的參考，都必須用 INSERTED 或 DELETED 前置詞來限定。

*
指定刪除、插入或更新動作所影響的所有資料行，都依照它們在資料表中的順序傳回。

例如，下列 DELETE 陳述式中的 OUTPUT DELETED.* 會傳回從 ShoppingCartItem 資料表中刪除的所有資料行：
```
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;
```

column_name
這是一個明確的資料行參考。任何指向修改之資料表的參考，都必須由 INSERTED 或 DELETED 前置詞來適當地加以限定，例如：INSERTED**.**column_name。

備註

您可以在單一 INSERT、UPDATE 或 DELETE 陳述式中，定義 OUTPUT <dml_select_list> 子句和 OUTPUT <dml_select_list> INTO { @table_variable | output_table } 子句。

附註：
除非另有指定，否則，指向 OUTPUT 子句的參考會同時參考 OUTPUT 子句和 OUTPUT INTO 子句。

在 INSERT 或 UPDATE 作業之後擷取識別或計算資料行值時，OUTPUT 子句可能很有用。

當 <dml_select_list> 包括計算資料行時，輸出資料表或資料表變數中對應的資料行並不是計算資料行。新資料行中的值是執行陳述式時所計算的值。

下列陳述式不支援 OUTPUT 子句：

參考本機資料分割檢視、分散式資料分割檢視或遠端資料表的 DML 陳述式。
包含 EXECUTE 陳述式的 INSERT 陳述式。

OUTPUT INTO 子句不能用來插入檢視或資料列集函數中。

資料表套用變更的順序以及資料列插入輸出資料表或資料表變數的順序，並無法保證能夠對應。

如果是在 UPDATE 陳述式中修改參數或變數，OUTPUT 子句一律會傳回執行陳述式之前的參數或變數值，而不是修改的值。

您可以搭配位於使用 WHERE CURRENT OF 語法的資料指標之 UPDATE 或 DELETE 陳述式來使用 OUTPUT。

若要避免不具決定性的行為，OUTPUT 子句就不可以包含會執行使用者或系統資料存取 (或經指定要執行這類存取) 的子查詢或使用者自訂函數。如果使用者自訂函數沒有結構描述繫結，通常會採用這些函數來執行資料存取。

觸發程序

從 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 子句時，它會使陳述式失敗。

資料類型

OUTPUT 子句支援大型物件資料類型：nvarchar(max)、varchar(max)、varbinary(max)、 text、ntext、image 和 xml。當您在 UPDATE 陳述式中利用 .WRITE 子句來修改 nvarchar(max)、varchar(max) 或 varbinary(max) 資料行時，如果參考了值完整的先後影像，便會傳回這些影像。在 OUTPUT 子句之 text、ntext 或 image 資料行的運算式中，不能出現 TEXTPTR( ) 函數。

佇列

您可以在利用資料表作為佇列的應用程式中使用 OUTPUT，也可以利用它來存放中繼結果集。也就是說，應用程式會不斷新增或移除資料表的資料列。下列範例會利用 DELETE 陳述式中的 OUTPUT 子句，將刪除的資料列傳回發出呼叫的應用程式。

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

這個範例會以單一動作從用作佇列的資料表中移除資料列，再將刪除的值傳回負責處理的應用程式。另外，也可能實作其他語意，如利用資料表來實作堆疊。不過，SQL Server 並無法保證使用 OUTPUT 子句的 DML 陳述式處理和傳回資料列的順序。應用程式負責決定是否併入適當的 WHERE 子句來保證所需要的語意，或瞭解當多個資料列可能符合 DML 作業的資格時，並無法保證順序。下列範例會使用子查詢，且假設唯一性是 DatabaseLogID 資料行的特性，以便實作所需要的排序語意。

USE tempdb
go

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

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

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

PRINT 'table1, before delete' 
SELECT * FROM table1

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

PRINT 'table1, after delete'
SELECT * FROM table1

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

DROP TABLE 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

附註：
如果您的狀況允許多個應用程式執行單一資料表的破壞性讀取，請在 UPDATE 和 DELETE 陳述式中使用 READPAST 資料表提示。這可以防止當另一個應用程式已在讀取資料表中第一個符合的記錄時，所可能出現的鎖定問題。

權限

利用 <dml_select_list> 來擷取的任何資料行，或 <scalar_expression> 所用的任何資料行，都需要 SELECT 權限。

<output_table> 所指定的任何資料表都需要 INSERT 權限。

範例

A. 搭配簡單 INSERT 陳述式來使用 OUTPUT INTO

下列範例將資料列插入 ScrapReason 資料表中，且利用 OUTPUT 子句，將陳述式的結果傳回 @MyTableVartable 變數中。由於 ScrapReasonID 資料行定義了 IDENTITY 屬性，因此，INSERT 陳述式並未指定這個資料行的值。不過請注意，Database Engine 針對這個資料行所產生的值，會在 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. 搭配 DELETE 陳述式使用 OUTPUT

下列範例會刪除 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

C. 搭配 UPDATE 陳述式使用 OUTPUT INTO

下列範例會將 Employee 資料表前 10 個資料列的 VacationHours 資料行更新 25%。OUTPUT 子句會將在 DELETED.VacationHours 資料行中套用 UPDATE 陳述式之前便已存在的 VacationHours 值，以及 INSERTED.VacationHours 資料行中更新的值傳回 @MyTableVartable 變數。

之後的兩個 SELECT 陳述式會傳回 @MyTableVar 中的值，以及 Employee 資料表中更新作業的結果。請注意，INSERTED.ModifiedDate 資料行中的結果不同於 Employee 資料表之 ModifiedDate 資料行的值。這是因為將 ModifiedDate 值更新成目前日期的 AFTER UPDATE 觸發程序是定義在 Employee 資料表上。不過，從 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

D. 使用 OUTPUT INTO 傳回運算式

下列範例是以範例 C 為基礎來建立的，它在 OUTPUT 子句中定義一個運算式，作為更新的 VacationHours 值和套用更新之前的 VacationHours 值之間的差異。這個運算式的值會傳回 VacationHoursDifference 資料行中的 @MyTableVartable 變數。

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

E. 在 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

F. 在 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

G. 搭配大型物件資料類型使用 OUTPUT INTO

下列範例會利用 .WRITE 子句，來更新 DocumentSummary (Production.Document 資料表中的 nvarchar(max) 資料行) 中的部分值。components 一字藉由指定用來取代的文字、現有資料中要被取代之文字的起始位置 (位移)，以及要取代的字元數 (長度) 來取代為 features 一字。這個範例利用 OUTPUT 子句，將 DocumentSummary 資料行的先後影像傳回 @MyTableVartable 變數。請注意，它會傳回 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

H. 在 INSTEAD OF 觸發程序中使用 OUTPUT

下列範例會利用觸發程序中的 OUTPUT 子句來傳回觸發程序作業的結果。首先是在 ScrapReason 資料表上建立一份檢視，之後，定義這份檢視的 INSTEAD OF INSERT 觸發程序，只讓使用者修改基底資料表的 Name 資料行。由於 ScrapReasonID 資料行是基底資料表中的 IDENTITY 資料行，觸發程序會忽略使用者提供的值。這使 Database Engine 能夠自動產生正確的值。另外，使用者提供的 ModifiedDate 值會被忽略，且會設為目前的日期。OUTPUT 子句會傳回實際插入 ScrapReason 資料表的值。

USE 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

I. 搭配識別和計算資料行使用 OUTPUT INTO

下列範例會建立 EmployeeSales 資料表，之後再利用含有 SELECT 陳述式的 INSERT 陳述式來擷取來源資料表中的資料，以插入幾個資料列。EmployeeSales 資料表包含一個識別資料行 (EmployeeID) 和一個計算資料行 (ProjectedSales)。由於這些值都是 SQL Server Database Engine 在插入作業期間所產生的，因此，這些資料行都不能定義在 @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

J. 在單一陳述式中使用 OUTPUT 和 OUTPUT INTO

下列範例根據 DELETE 陳述式的 FROM 子句所定義的搜尋準則，來刪除 ProductProductPhoto 資料表中的資料列。OUTPUT INTO 子句會將所刪除資料表中的資料行 (DELETED.ProductID、DELETED.ProductPhotoID) 及 Product 資料表中的資料行傳回 @MyTableVartable 變數。FROM 子句利用 Product 資料表來指定要刪除的資料列。OUTPUT 子句會將 DELETED.ProductID、DELETED.ProductPhotoID 資料行及從 ProductProductPhoto 資料表中刪除資料列的日期和時間，傳回給發出呼叫的應用程式。

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

請參閱

參考

DELETE (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
資料表 (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
sp_configure (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	變更的內容：移除關於在 scalar_expression 中使用子查詢的不正確內容。為避免產生不一致的資料列結果，目前已不再允許在 OUTPUT 子句中使用子查詢。