OUTPUT 子句 (Transact-SQL)
適用於:SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體
從 INSERT、UPDATE、DELETE 或 MERGE 陳述式所影響的每個資料列傳回資訊,或傳回以 INSERT、UPDATE、DELETE 或 MERGE 陳述式所影響的每個資料列為基礎的運算式。 這些結果可以傳回給負責處理的應用程式,以便用在確認訊息、封存或其他這類應用程式需求等用途上。 這些結果也可以插入資料表或資料表變數中。 此外,您可以在巢狀 INSERT、UPDATE、DELETE 或 MERGE 語句中擷取子句的結果 OUTPUT
,並將這些結果插入目標數據表或檢視中。
注意
具有 OUTPUT
子句的 UPDATE、INSERT 或 DELETE 語句會傳回數據列給用戶端,即使語句發生錯誤並回復也一樣。 當您執行 語句時發生任何錯誤時,不應該使用結果。
用於:
Syntax
<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
注意
若要檢視 SQL Server 2014 (12.x) 和更早版本的 Transact-SQL 語法,請參閱舊版文件。
引數
@table_variable
指定 table 變數以將傳回的資料列插入其中,而不傳回給呼叫端。 @table_variable必須在 INSERT、UPDATE、DELETE 或 MERGE 語句之前宣告。
如果未 指定column_list , 數據表 變數必須具有與結果集相同的數據行數目 OUTPUT
。 識別和計算資料行例外,它們必須被略過。 如果指定 column_list,任何省略的資料行都必須允許 Null 值或已具有指派的預設值。
如需有關 table 變數的詳細資訊,請參閱 table (Transact-SQL)。
output_table
指定資料表,讓傳回的資料列插入其中,而不要傳回給呼叫端。 output_table 可以是暫存資料表。
如果未 指定column_list ,數據表的數據行數目 OUTPUT
必須與結果集相同。 識別和計算資料行例外。 它們必須被略過。 如果指定 column_list,任何省略的資料行都必須允許 Null 值或已具有指派的預設值。
output_table無法:
- 啟用它所定義的觸發程序。
- 參與 FOREIGN KEY 條件約束的任何一端。
- 有 CHECK 條件約束或啟用的規則。
column_list
INTO 子句之目標數據表上的選擇性數據行名稱清單。 它類似於 INSERT 陳述式中所允許使用的資料行清單。
scalar_expression
評估為單一值的符號和運算子的任何組合。 scalar_expression中不允許聚合函數。
任何指向修改的資料表中之資料行的參考,都必須用 INSERTED 或 DELETED 前置詞來限定。
column_alias_identifier
用來參考數據行名稱的替代名稱。
DELETED
數據行前置詞,指定更新或刪除作業所刪除的值。 前置詞是 DELETED 的資料行反映 UPDATE、DELETE 或 MERGE 陳述式完成之前的值。
DELETED 無法與 INSERT 語句中的 子句搭配 OUTPUT
使用。
INSERTED
數據行前置詞,指定插入或更新作業所新增的值。 前置詞是 INSERTED 的資料行反映 UPDATE、INSERT 或 MERGE 陳述式完成之後、觸發程序執行之前的值。
INSERTED 不能與 DELETE 語句中的 子句搭配 OUTPUT
使用。
from_table_name
數據行前置詞,指定要更新或刪除之數據列之 DELETE、UPDATE 或 MERGE 語句之 FROM 子句中包含的數據表。
如果 FROM 子句也指定了所修改的資料表,任何指向這份資料表中之資料行的參考,都必須用 INSERTED 或 DELETED 前置詞來限定。
*
*
指定所有受刪除、插入或更新動作影響的數據行都會依照數據表中存在的順序傳回。
例如,下列 DELETE 陳述式中的 OUTPUT DELETED.*
會傳回從 ShoppingCartItem
資料表中刪除的所有資料行:
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
column_name
明確的數據行參考。 若要參考所要修改的資料表,必須依據適用情況,以 INSERTED 或 DELETED 前置詞正確地限定該參考,例如:INSERTED.column_name。
$action
僅適用於 MERGE 語句。 根據在該數據列上執行的動作,在 MERGE 語句中OUTPUT
指定 nvarchar(10) 類型的數據行,該子句會針對每個數據列傳回三個值之一:『INSERT』、『UPDATE』 或 『DELETE』。
備註
OUTPUT <dml_select_list>
子句和 OUTPUT <dml_select_list> INTO { @table_variable | output_table }
子句可以在單一 INSERT、UPDATE、DELETE 或 MERGE 語句中定義。
注意
除非另有指定,否則子句的 OUTPUT
參考會同時參考 OUTPUT
子句和 OUTPUT INTO
子句。
OUTPUT
子句在 INSERT 或 UPDATE 作業之後擷取識別或計算數據行的值可能很有用。
當計算數據列包含在 中 <dml_select_list>
時,輸出數據表或數據表變數中的對應數據行不是計算數據行。 新資料行中的值是執行陳述式時所計算的值。
不保證變更套用至數據表的順序,以及數據列插入輸出數據表或數據表變數的順序將會對應。
如果參數或變數修改為UPDATE語句的一部分, OUTPUT
子句一律會傳回參數或變數的值,如同在語句執行之前,而不是修改的值。
您可以在 OUTPUT
使用 WHERE CURRENT OF 語法的數據指標上使用 UPDATE 或 DELETE 語句。
下列語句不支援 子 OUTPUT
句:
參考本機資料分割檢視、分散式資料分割檢視或遠端資料表的 DML 陳述式。
包含 EXECUTE 陳述式的 INSERT 陳述式。
當資料庫相容性層級設定為 100 時,子句中不允許
OUTPUT
全文檢索述詞。OUTPUT INTO
子句無法用來插入檢視或數據列集函式。如果使用者定義函數包含
OUTPUT
具有數據表做為其目標的 INTO 子句,則無法建立。
若要防止非決定性行為, OUTPUT
子句不能包含下列參考:
執行使用者或系統資料存取或假設會執行這類存取的子查詢或使用者定義函數。 如果未系結架構,則會假設使用者定義函式會執行數據存取。
當檢視表或嵌入資料表值函式中的資料行是由下列其中一種方法所定義時:
子查詢。
執行使用者或系統資料存取的使用者定義函數,或假設會執行這類存取的使用者定義函數。
包含使用者定義函數的計算資料行,而該函數會在其定義中執行使用者或系統資料存取。
當 SQL Server 在 子句中
OUTPUT
偵測到這類數據行時,就會引發錯誤 4186。
將數據從 OUTPUT 子句傳回的數據插入數據表
當您在巢狀 INSERT、UPDATE、DELETE 或 MERGE 語句中擷取 子句的結果 OUTPUT
,並將這些結果插入目標數據表時,請記住下列資訊:
整個作業是不可部分完成的。 INSERT 語句和包含
OUTPUT
子句執行的巢狀 DML 語句,或整個語句都失敗。下列限制適用於外部 INSERT 陳述式的目標:
目標不能是遠端數據表、檢視表或通用數據表運算式。
目標不能有FOREIGNKEY條件約束,或由FOREIGNKEY條件約束參考。
無法在目標上定義觸發程式。
目標無法參與事務複製的合併式複寫或可更新的訂閱。
下列限制適用於巢狀 DML 陳述式:
目標不能是遠端數據表或數據分割檢視。
來源本身不能包含
<dml_table_source>
子句。
包含
<dml_table_source>
子句的 INSERT 語句不支援 子OUTPUT INTO
句。@@ROWCOUNT
只會傳回外部 INSERT 語句所插入的數據列。@@IDENTITY
、SCOPE_IDENTITY
和會IDENT_CURRENT
傳回只由巢狀 DML 語句產生的識別值,而不是外部 INSERT 語句所產生的值。查詢通知會將此陳述式視為單一實體,而且所建立的任何訊息類型都將成為巢狀 DML 的類型,即使重大變更來自外部 INSERT 陳述式本身也一樣。
在 子句中
<dml_table_source>
,SELECT 和 WHERE 子句不能包含子查詢、聚合函數、排名函數、全文檢索述詞、執行數據存取的使用者定義函式或函TEXTPTR()
式。
平行處理原則
傳 OUTPUT
回結果給客戶端或數據表變數的 子句一律會使用序列計劃。
在資料庫設定為相容性層級 130 或更高版本的內容中,如果 INSERT...SELECT
作業使用 WITH (TABLOCK)
SELECT 語句的提示,也會使用 OUTPUT...INTO
插入暫存或用戶數據表,則 的目標數據表 INSERT...SELECT
會根據子樹成本而符合平行處理原則的資格。 子句中所 OUTPUT INTO
參考的目標數據表不符合平行處理原則的資格。
觸發程序
從 OUTPUT
傳回的數據行會反映數據,因為它是在 INSERT、UPDATE 或 DELETE 語句完成之後,但在執行觸發程式之前。
如果是 INSTEAD OF 觸發程序,便會依照實際發生 INSERT、UPDATE 或 DELETE 的情況來產生傳回的結果,即使觸發程序作業結果並沒有進行任何修改也是如此。 如果在觸發程式主體內使用包含 OUTPUT
子句的語句,則必須使用數據表別名來參考插入和刪除的數據表,以避免使用與相關聯的 OUTPUT
INSERTED 和 DELETED 數據表複製數據行參考。
如果指定 子 OUTPUT
句而不指定 INTO 關鍵詞,DML 作業的目標就不能針對指定的 DML 動作定義任何已啟用的觸發程式。 例如,如果 OUTPUT
子句是在UPDATE語句中定義,目標數據表就不能有任何啟用的UPDATE觸發程式。
sp_configure
如果已設定選項不允許觸發程式的結果,OUTPUT
沒有 INTO 子句的 子句會在從觸發程式內叫用語句時失敗。
資料類型
子OUTPUT
句支援大型對象數據類型:nvarchar(max)、varchar(max)、varbinary(max)、text、ntext、image 和 xml。 當您在 UPDATE 語句中使用 .WRITE
子句來修改 nvarchar(max)、varchar(max)或 varbinary(max) 數據行時,如果參考值影像之前和之後,就會傳回完整的值。 函TEXTPTR()
式無法在 子句中的 text、ntext 或 image 數據行上顯示為表達式的OUTPUT
一部分。
佇列
您可以在 OUTPUT
使用資料表做為佇列的應用程式中使用 ,或用來保存中繼結果集。 也就是說,應用程式會不斷新增或移除資料表的資料列。 下列範例會使用 OUTPUT
DELETE 語句中的 子句,將已刪除的數據列傳回給呼叫的應用程式。
USE AdventureWorks2022;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO
這個範例會以單一動作從用作佇列的資料表中移除資料列,再將刪除的值傳回負責處理的應用程式。 另外,也可能實作其他語意,如利用資料表來實作堆疊。 不過,SQL Server 並不保證 DML 語句使用 OUTPUT
子句處理和傳回數據列的順序。 應用程式負責決定是否併入適當的 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;
以下是結果:
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
子句,將陳述式的結果傳回 @MyTableVar
資料表變數。 由於數據 ScrapReasonID
行是以 IDENTITY 屬性定義,因此該數據行的 INSERT
語句中未指定值。 不過,資料庫引擎 針對該數據行所產生的值會在 數據行 的 INSERTED.ScrapReasonID
子句中OUTPUT
傳回。
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
NewScrapReasonID 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 NewScrapReasonID, 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 AdventureWorks2022;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;
--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO
C. 搭配 UPDATE 語句使用 OUTPUT INTO
下列範例會將 VacationHours
資料表前 10 個資料列的 Employee
資料行更新 25%。 OUTPUT
子句會將在 DELETED.VacationHours
資料行中套用 UPDATE
陳述式之前便已存在的 VacationHours
值,以及 INSERTED.VacationHours
資料行中更新的值傳回給 @MyTableVar
資料表變數。
接下來有兩個 SELECT
語句,傳回 中的 @MyTableVar
值,以及數據表中 Employee
更新作業的結果。
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
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.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. 使用 OUTPUT INTO 傳回表達式
下列範例是以範例 C 為基礎所建立,它在 OUTPUT
子句中定義一個運算式,當做更新的 VacationHours
值和套用更新之前的 VacationHours
值之間的差異。 此運算式的值會傳回給 VacationHoursDifference
資料行中的 @MyTableVar
資料表變數。
USE AdventureWorks2022;
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,
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) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
E. 在 UPDATE 語句中搭配from_table_name使用 OUTPUT INTO
下列範例會針對具有指定 ProductID
和 ScrapReasonID
的所有工單,更新 WorkOrder
資料表中的 ScrapReasonID
資料行。 OUTPUT INTO
子句會從更新的資料表 (WorkOrder
) 傳回值,也會從 Product
傳回值。 Product
子句利用 FROM
資料表來指定要更新的資料列。 由於 WorkOrder
資料表定義了 AFTER UPDATE
觸發程序,因此,需要 INTO
關鍵字。
USE AdventureWorks2022;
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
下列範例根據 ProductProductPhoto
陳述式的 FROM
子句所定義的搜尋準則,來刪除 DELETE
資料表中的資料列。 OUTPUT
子句會傳回所刪除的資料表資料行 (DELETED.ProductID
、DELETED.ProductPhotoID
) 及 Product
資料表中的資料行。 FROM
子句利用這份資料表來指定要刪除的資料列。
USE AdventureWorks2022;
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
下列範例會利用 DocumentSummary
子句,來更新 nvarchar(max)
(Production.Document
資料表中的 .WRITE
資料行) 中的部分值。 components
一字藉由指定用來取代的文字、現有資料中要被取代之文字的起始位置 (位移),以及要取代的字元數 (長度) 來取代為 features
一字。 此範例會使用 OUTPUT
子句,將 DocumentSummary
資料行的前後影像傳回給 @MyTableVar
資料表變數。 傳回資料行影像前後的完整 DocumentSummary
。
USE AdventureWorks2022;
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';
SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
H. 在 INSTEAD OF 觸發程式中使用 OUTPUT
下列範例會利用觸發程序中的 OUTPUT
子句來傳回觸發程序作業的結果。 首先在 ScrapReason
資料表上建立檢視,然後在此檢視上定義 INSTEAD OF INSERT
觸發程序,只讓使用者修改基底資料表的 Name
資料行。 由於 ScrapReasonID
資料行是基底資料表中的 IDENTITY
資料行,觸發程序會忽略使用者提供的值。 這會使資料庫引擎自動產生正確的值。 另外,使用者提供的 ModifiedDate
值會被忽略,且會設為目前的日期。 OUTPUT
子句會傳回實際插入 ScrapReason
資料表的值。
USE AdventureWorks2022;
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
資料表,之後再利用含有 INSERT
陳述式的 SELECT
陳述式來擷取來源資料表中的資料,以插入幾個資料列。 EmployeeSales
資料表包含一個識別欄位 (EmployeeID
) 和一個計算資料行 (ProjectedSales
)。
USE AdventureWorks2022;
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 (
EmployeeID INT NOT NULL,
LastName NVARCHAR(20) NOT NULL,
FirstName NVARCHAR(20) NOT NULL,
CurrentSales MONEY NOT NULL,
ProjectedSales MONEY NOT NULL
);
INSERT INTO dbo.EmployeeSales (
LastName,
FirstName,
CurrentSales
)
OUTPUT INSERTED.EmployeeID,
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
INSERTED.ProjectedSales
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 EmployeeID,
LastName,
FirstName,
CurrentSales,
ProjectedSales
FROM @MyTableVar;
GO
SELECT EmployeeID,
LastName,
FirstName,
CurrentSales,
ProjectedSales
FROM dbo.EmployeeSales;
GO
J. 在單一語句中使用 OUTPUT 和 OUTPUT INTO
下列範例根據 ProductProductPhoto
陳述式的 FROM
子句所定義的搜尋準則,來刪除 DELETE
資料表中的資料列。 OUTPUT INTO
子句會將所要刪除之資料表中的資料行 (DELETED.ProductID
、DELETED.ProductPhotoID
) 及 Product
資料表中的資料行傳回給 @MyTableVar
資料表變數。 Product
子句利用 FROM
資料表來指定要刪除的資料列。 OUTPUT
子句會將 DELETED.ProductID
、DELETED.ProductPhotoID
資料行及從 ProductProductPhoto
資料表中刪除資料列的日期和時間,傳回給發出呼叫的應用程式。
USE AdventureWorks2022;
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
INNER 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
K. 插入從 OUTPUT 子句傳回的數據
下列範例將擷取從 OUTPUT
陳述式的 MERGE
子句中傳回的資料,並將該資料插入另一個資料表中。 MERGE
陳述式會根據在 Quantity
資料表中處理的順序,每天更新 ProductInventory
資料表的 SalesOrderDetail
資料行。 它也會刪除庫存下降至 0
或減少的產品數據列。 此範例會擷取已刪除的資料列,並將其插入另一個資料表 ZeroInventory
,該資料表會追蹤沒有存貨的產品。
USE AdventureWorks2022;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (
DeletedProductID INT,
RemovedOnDate DATETIME
);
GO
INSERT INTO Production.ZeroInventory (
DeletedProductID,
RemovedOnDate
)
SELECT ProductID,
GETDATE()
FROM (
MERGE Production.ProductInventory AS pi
USING (
SELECT ProductID,
SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = '20070401'
GROUP BY ProductID
) AS src(ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED
AND pi.Quantity - src.OrderQty <= 0
THEN
DELETE
WHEN MATCHED
THEN
UPDATE
SET pi.Quantity = pi.Quantity - src.OrderQty
OUTPUT $ACTION,
DELETED.ProductID
) AS Changes(Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID,
RemovedOnDate
FROM Production.ZeroInventory;
GO