OUTPUT 句 (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
INSERT、UPDATE、DELETE、または MERGE の各ステートメントの影響を受ける行の情報や、それらに基づく式を返します。 これらの結果は処理アプリケーションに返され、確認メッセージの表示、アーカイブ化、その他のアプリケーション要件で使用することができます。 また、結果をテーブルまたはテーブル変数に挿入することもできます。 さらに、入れ子になった INSERT、UPDATE、DELETE、または MERGE ステートメント内の OUTPUT
句の結果を取得してターゲット テーブルまたはビューに挿入することもできます。
注意
UPDATE、INSERT、または DELETE ステートメントに OUTPUT
句があると、ステートメントでエラーが発生してロールバックされた場合にも、クライアントに行が返されます。 ステートメントの実行時にエラーが発生した場合は、結果を使用しないでください。
使用される場所:
構文
<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
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
@table_variable
返される行を呼び出し元に返さずにテーブルに挿入する場合に、挿入先となる table 変数を指定します。 @table_variable は、INSERT、UPDATE、DELETE、または MERGE ステートメントの前に宣言する必要があります。
column_list を指定しない場合は、table 変数の列の数は OUTPUT
の結果セットと同じである必要があります。 ただし、ID 列と計算列はスキップされるため、同じである必要はありません。 column_list を指定した場合は、省略された列は NULL 値を許容しているか、既定値が割り当てられている必要があります。
table 変数の詳細については、「table (Transact-SQL)」を参照してください。
output_table
返される行を呼び出し元に返さずにテーブルに挿入する場合に、挿入先となるテーブルを指定します。 output_table には一時テーブルを指定することもできます。
column_list を指定しない場合は、table の列の数は OUTPUT
の結果セットと同じである必要があります。 ID 列と計算列は同じである必要はありません。 スキップされるためです。 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 ステートメントが完了する前の値を反映します。
INSERT ステートメント内で DELETED を OUTPUT
句と共に使うことはできません。
INSERTED
挿入操作または更新操作で追加される値を指定する列プレフィックスです。 INSERTED プレフィックスの付いた列は、UPDATE、INSERT、または MERGE ステートメントが完了した後の、トリガーが実行される前の値を反映します。
DELETE ステートメント内で INSERTED を OUTPUT
句と共に使うことはできません。
from_table_name
DELETE、UPDATE、または MERGE ステートメントの FROM 句に含まれるテーブルを指定する列プレフィックスです。更新または削除する行を指定するために使用します。
変更するテーブルが FROM 句でも指定されている場合には、そのテーブルの列への参照は、すべて INSERTED プレフィックスまたは DELETED プレフィックスで修飾する必要があります。
*
*
は、削除、挿入、または更新操作で影響を受けるすべての列を、テーブル中に存在する順序で返すよう指示します。
たとえば、次の DELETE ステートメントの OUTPUT DELETED.*
は、ShoppingCartItem
テーブルから削除されるすべての列を返します。
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
column_name
明示的な列参照です。 変更するテーブルへのすべての参照は、たとえば INSERTED.column_name のように、INSERTED または DELETED プレフィックスで正しく修飾されている必要があります。
$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 操作の後で ID 列や計算列の値を取得するのに便利です。
計算列が <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 ステートメントの対象には次の制限が適用されます。
リモート テーブル、ビュー、または共通テーブル式をターゲットにすることはできません。
ターゲットに FOREIGN KEY 制約を含めたり、ターゲットを FOREIGN KEY 制約で参照したりすることはできません。
ターゲットに対してトリガーを定義することはできません。
ターゲットを、マージ レプリケーションや、トランザクション レプリケーションの更新可能なサブスクリプションに加えることはできません。
入れ子になった DML ステートメントには次の制限が適用されます。
リモート テーブルまたはパーティション ビューをターゲットにすることはできません。
ソース自体に
<dml_table_source>
句を含めることはできません。
OUTPUT INTO
句は、<dml_table_source>
句を含む INSERT ステートメントではサポートされません。@@ROWCOUNT
からは、外部の INSERT ステートメントによって挿入された行のみが返されます。@@IDENTITY
、SCOPE_IDENTITY
、IDENT_CURRENT
は、入れ子になった DML ステートメントによって生成された ID 値のみを返し、外部の INSERT ステートメントによって生成された ID 値は返しません。クエリ通知ではステートメントは単一のエンティティとして扱われ、作成されるメッセージの種類は入れ子になった DML の種類になります。これは、外部の INSERT ステートメント自体から大きな変更が加えられた場合でも同様です。
<dml_table_source>
句では、SELECT 句および WHERE 句に、サブクエリ、集計関数、順位付け関数、フルテキスト述語、データにアクセスするユーザー定義関数、またはTEXTPTR()
関数を含めることはできません。
Parallelism
結果をクライアントに返す OUTPUT
句、またはテーブル変数は、常に直列プランを使用します。
互換性レベル 130 以上に設定されたデータベースのコンテキストでは、INSERT...SELECT
操作で SELECT ステートメントに WITH (TABLOCK)
ヒントが使用され、OUTPUT...INTO
を使用して一時テーブルまたはユーザー テーブルに挿入された場合、サブツリーのコストに応じて、INSERT...SELECT
のターゲット テーブルが並列処理の対象となります。 OUTPUT INTO
句で参照されているターゲット テーブルは、並列処理の対象になりません。
トリガー
OUTPUT
から返される列は、INSERT、UPDATE、または DELETE ステートメントが完了した後、トリガーが実行される前のデータを反映します。
INSTEAD OF トリガーでは、トリガー操作の結果変更が行われない場合でも、INSERT、UPDATE、または DELETE が実際に行われたかのように返される結果が生成されます。 OUTPUT
句を含むステートメントがトリガー本体の中で使用されている場合、トリガーの inserted テーブルおよび deleted テーブルを参照するためには、テーブルの別名を使用する必要があります。これにより、OUTPUT
に関連付けられている INSERTED テーブルおよび DELETED テーブルで列参照が重複するのを避けることができます。
INTO キーワードを指定せずに OUTPUT
句を指定すると、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) の列を変更すると、参照されていれば、値の完全な前イメージと後イメージが返されます。 TEXTPTR()
関数を、OUTPUT
句内の text、ntext、または image 列に対する式の一部として使用することはできません。
キュー
OUTPUT
を、テーブルをキューとして使うアプリケーションで使用したり、中間結果セットを保持するために使用したりできます。 つまり、アプリケーションは、テーブルに対して、常に行の追加または削除を行っています。 次の例では、DELETE ステートメント内で OUTPUT
句を使い、削除された行を呼び出し元アプリケーションに返します。
USE AdventureWorks2022;
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 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
Note
複数のアプリケーションの同じテーブルへの破壊的な読み取りを許可する場合は、UPDATE ステートメントおよび DELETE ステートメントで READPAST テーブル ヒントを使用します。 これにより、テーブル内の最初の該当レコードを別のアプリケーションが既に読み込み中である場合に発生するロックの問題が起こらなくなります。
アクセス許可
<dml_select_list>
で取得する列や、<scalar_expression>
で使用する列に対する SELECT 権限が必要です。
<output_table>
で指定するテーブルに対する INSERT 権限が必要です。
例
A. OUTPUT INTO を INSERT ステートメントで使う
次の例では、ScrapReason
テーブルに 1 行を挿入し、OUTPUT
句を使用してステートメントの結果を @MyTableVar
テーブル変数に返します。 ScrapReasonID
列が IDENTITY プロパティで定義されているため、INSERT
ステートメントではこの列の値を指定していません。 ただし、データベース エンジン によってこの列用に生成された値が、OUTPUT
句で INSERTED.ScrapReasonID
列に返されます。
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. OUTPUT を DELETE ステートメントで使う
次の例では、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. OUTPUT INTO を UPDATE ステートメントで使う
次の例では、VacationHours
テーブル内の最初の 10 個の行について、Employee
列を 25% 増しに更新します。 OUTPUT
句は、VacationHours
を適用する前の UPDATE
列の DELETED.VacationHours
の値と、INSERTED.VacationHours
列の更新後の値を @MyTableVar
テーブル変数に返します。
その後に、@MyTableVar
内の値、および Employee
テーブルの更新操作の結果を返す 2 つの SELECT
ステートメントが続きます。
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 を基に構築され、更新後の VacationHours
の値と更新が適用される前の VacationHours
の値の差として、OUTPUT
句の中で式を定義しています。 この式の値は、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. OUTPUT INTO を UPDATE ステートメント内で from_table_name と共に使う
次の例は、WorkOrder
テーブルの ScrapReasonID
列の、指定された ProductID
と 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. OUTPUT INTO を DELETE ステートメント内で from_table_name と共に使う
次の例では、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. OUTPUT を INSTEAD OF トリガー内で使う
次の例では、トリガー内で 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 を ID 列および計算列と共に使う
次の例では、EmployeeSales
テーブルを作成し、INSERT
ステートメントを使用してこのテーブルに複数行を挿入します。基になるテーブルからデータを取得するために、SELECT
ステートメントも使用します。 EmployeeSales
テーブルには、ID 列 (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
ステートメントは、SalesOrderDetail
テーブル内で処理された注文に基づいて、ProductInventory
テーブルの Quantity
列を毎日更新します。 また、在庫が 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