OUTPUT 子句 (Transact-SQL)
適用於:Microsoft Fabric 中的 SQL Server
Azure SQL 資料庫 Azure SQL 受控執行個體
SQL 資料庫
根據 、、 DELETE
或 MERGE
語句所影響INSERT
UPDATE
的每個數據列,從 或表達式傳回資訊。 這些結果可以傳回給負責處理的應用程式,以便用在確認訊息、封存或其他這類應用程式需求等用途上。 這些結果也可以插入資料表或資料表變數中。 此外,您可以在巢狀 、、 DELETE
或 MERGE
語句中擷取 子句的結果OUTPUT
,並將這些結果插入目標數據表或UPDATE
檢視中。INSERT
注意
具有 UPDATE
子句的 OUTPUT
、 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
指定 table 變數以將傳回的資料列插入其中,而不傳回給呼叫端。 @table_variable必須在 、 UPDATE
或 DELETE
MERGE
語句之前INSERT
宣告。
如果未 指定column_list , 數據表 變數必須具有與結果集相同的數據行數目 OUTPUT
。 識別和計算資料行例外,它們必須被略過。 如果指定 column_list,任何省略的資料行都必須允許 Null 值或已具有指派的預設值。
如需數據表變數的詳細資訊,請參閱 table。
指定資料表,讓傳回的資料列插入其中,而不要傳回給呼叫端。 output_table可能是臨時表。
如果未 指定column_list ,數據表的數據行數目 OUTPUT
必須與結果集相同。 識別和計算資料行例外,它們必須被略過。 如果指定 column_list,任何省略的資料行都必須允許 Null 值或已具有指派的預設值。
output_table無法:
- 啟用它所定義的觸發程序。
- 參與條件約束的任一
FOREIGN KEY
端。 - 具有
CHECK
條件約束或啟用的規則。
子句目標數據表 INTO
上的選擇性數據行名稱清單。 這類似於 INSERT 語句中允許的數據行清單。
評估為單一值的符號和運算子的任何組合。 scalar_expression中不允許聚合函數。
要修改之數據表中數據行的任何參考都必須以 或 DELETED
前置詞INSERTED
限定。
用來參考數據行名稱的替代名稱。
數據行前置詞,指定更新或刪除作業所刪除的值,以及任何未隨著目前作業變更的現有值。 前面加上的數據DELETED
行會反映、 DELETE
或 MERGE
語句完成之前UPDATE
的值。
DELETED
無法與語句中的 INSERT
子句搭配OUTPUT
使用。
數據行前置詞,指定插入或更新作業所新增的值,以及任何未隨著目前作業變更的現有值。 前面加上 INSERTED
的數據行會反映 、 INSERT
或 MERGE
語句完成之後UPDATE
的值,但在執行觸發程式之前。
INSERTED
無法與語句中的 DELETE
子句搭配OUTPUT
使用。
數據行前置詞,指定要更新或刪除之數據列的DELETE
、 UPDATE
或 MERGE
語句子句中包含的FROM
數據表。
如果子句中也指定了要修改的 FROM
數據表,該數據表中數據行的任何參考都必須以 INSERTED
或 DELETED
前置詞限定。
星號 (*
) 會指定受刪除、插入或更新動作影響的所有數據行都會依照資料表中存在的順序傳回。
例如,在下列DELETE
語句中,OUTPUT DELETED.*
會傳回從ShoppingCartItem
數據表中刪除的所有數據行:
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
明確的數據行參考。 修改之數據表的任何參考都必須適當地由 INSERTED
或 DELETED
前置詞正確限定,例如: INSERTED.<column_name>
。
僅適用於 MERGE
語句。 在語句中OUTPUT
MERGE
指定 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
句在 或 UPDATE
作業之後INSERT
擷取識別或計算數據行的值可能很有用。
當計算數據列包含在 中 <dml_select_list>
時,輸出數據表或數據表變數中的對應數據行不是計算數據行。 新資料行中的值是執行陳述式時所計算的值。
變更套用至數據表的順序,以及數據列插入輸出數據表或數據表變數的順序,不保證對應。
如果參數或變數修改為語句的一 UPDATE
部分, OUTPUT
子句一律會傳回參數或變數的值,如同語句執行前的值,而不是修改的值。
您可以在OUTPUT
UPDATE
使用WHERE CURRENT OF
語法的數據指標上使用 或 DELETE
語句。
下列語句不支援 子 OUTPUT
句:
參考本機資料分割檢視、分散式資料分割檢視或遠端資料表的 DML 陳述式。
INSERT
包含語句的EXECUTE
語句。當資料庫相容性層級設定為 100 時,子句中不允許
OUTPUT
全文檢索述詞。OUTPUT INTO
子句無法用來插入檢視或數據列集函式。如果使用者定義函數包含
OUTPUT INTO
具有數據表做為其目標的子句,則無法建立。
若要防止非決定性行為, OUTPUT
子句不能包含下列參考:
執行使用者或系統資料存取或假設會執行這類存取的子查詢或使用者定義函數。 如果未系結架構,則會假設使用者定義函式會執行數據存取。
當檢視表或嵌入資料表值函式中的資料行是由下列其中一種方法所定義時:
子查詢。
執行使用者或系統資料存取的使用者定義函數,或假設會執行這類存取的使用者定義函數。
包含使用者定義函數的計算資料行,而該函數會在其定義中執行使用者或系統資料存取。
當 SQL Server 在 子句中
OUTPUT
偵測到這類數據行時,就會引發錯誤 4186。
當您在巢狀 、、 DELETE
或 MERGE
語句中擷取 子句的結果OUTPUT
,並將這些結果插入目標數據表時,UPDATE
請記住下列資訊:INSERT
整個作業是不可部分完成的。
INSERT
語句和包含OUTPUT
子句執行的巢狀 DML 語句,或整個語句失敗。下列限制適用於外部
INSERT
語句的目標:目標不能是遠端數據表、檢視表或通用數據表運算式。
目標不能有
FOREIGN KEY
條件約束,或由FOREIGN KEY
條件約束參考。無法在目標上定義觸發程式。
目標無法參與事務複製的合併式複寫或可更新的訂閱。
下列限制適用於巢狀 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
傳回的數據行會反映在 、 UPDATE
或 DELETE
語句完成之後INSERT
的數據,但在執行觸發程式之前。
針對INSTEAD OF
觸發程式,即使觸發程式作業的結果沒有進行任何修改,也會產生傳回的結果,INSERT
UPDATE
就好像 、 或 DELETE
已實際發生一樣。 如果在觸發程式主體內使用包含 OUTPUT
子句的語句,則必須使用數據表別名來參考插入和刪除的觸發程式,以避免與相關聯的 OUTPUT
和 DELETED
數據表重複數據行參考INSERTED
。
如果指定 子 OUTPUT
句而不指定 INTO
關鍵詞,DML 作業的目標就無法針對指定的 DML 動作定義任何已啟用的觸發程式。 例如,如果 OUTPUT
子句是在語句中 UPDATE
定義,則目標數據表不能有任何啟用的 UPDATE
觸發程式。
sp_configure
如果已設定選項不允許觸發程式的結果,OUTPUT
沒有 INTO
子句的 子句會導致語句在觸發程式內叫用時失敗。
子OUTPUT
句支援大型對象數據類型:nvarchar(max)、varchar(max)、varbinary(max)、text、ntext、image 和 xml。 當您在 語句中使用 .WRITE
UPDATE
子句來修改 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
注意
READPAST
如果您的案例允許多個應用程式執行一個數據表的破壞性讀取,請使用 和 DELETE
語句中的UPDATE
數據表提示。 這可以防止當另一個應用程式已在讀取資料表中第一個符合的記錄時,所可能出現的鎖定問題。
SELECT
擷取 <dml_select_list>
或用於 <scalar_expression>
的任何數據行都需要許可權。
INSERT
在中指定的 <output_table>
任何數據表上都需要許可權。
本文 Transact-SQL 程式碼範例使用 AdventureWorks2022
或 AdventureWorksDW2022
範例資料庫,從 Microsoft SQL Server Samples 和 Community Projects (Microsoft SQL Server 範例和社群專案)首頁即可下載。
下列範例會將資料列插入 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
下列範例會刪除 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
下列範例會將 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
下列範例是以範例 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
下列範例會針對具有指定 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
下列範例根據 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
下列範例會使用 .WRITE
子句,更新 數據表中 nvarchar(max) 數據行中的Production.Document
DocumentSummary
部分值。 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
下列範例會利用觸發程序中的 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
下列範例會建立 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
下列範例根據 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
下列範例將擷取從 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