OUTPUT 句 (Transact-SQL)

[アーティクル]
04/17/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance

INSERT、UPDATE、DELETE、または MERGE の各ステートメントの影響を受ける行の情報や、それらに基づく式を返します。これらの結果は処理アプリケーションに返され、確認メッセージの表示、アーカイブ化、その他のアプリケーション要件で使用することができます。また、結果をテーブルまたはテーブル変数に挿入することもできます。さらに、入れ子になった INSERT、UPDATE、DELETE、または MERGE ステートメント内の OUTPUT 句の結果を取得してターゲットテーブルまたはビューに挿入することもできます。

注意

UPDATE、INSERT、または DELETE ステートメントに OUTPUT 句があると、ステートメントでエラーが発生してロールバックされた場合にも、クライアントに行が返されます。ステートメントの実行時にエラーが発生した場合は、結果を使用しないでください。

使用される場所:

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

OUTPUT 句 (Transact-SQL)

構文

引数

@table_variable

output_table

column_list

scalar_expression

column_alias_identifier

DELETED

INSERTED

from_table_name

`*`

column_name

$action

解説

OUTPUT 句から返されたデータをテーブルに挿入する

Parallelism

トリガー

データ型

キュー

アクセス許可

例

A. OUTPUT INTO を INSERT ステートメントで使う

B. OUTPUT を DELETE ステートメントで使う

C. OUTPUT INTO を UPDATE ステートメントで使う

D. OUTPUT INTO を使って式を返す

E. OUTPUT INTO を UPDATE ステートメント内で from_table_name と共に使う

F. OUTPUT INTO を DELETE ステートメント内で from_table_name と共に使う

G. OUTPUT INTO をラージオブジェクトデータ型と共に使う

H. OUTPUT を INSTEAD OF トリガー内で使う

I. OUTPUT INTO を ID 列および計算列と共に使う

J. OUTPUT と OUTPUT INTO を単一のステートメント内で使う

K. OUTPUT 句から返されたデータを挿入する

次の手順

フィードバック

フィードバック

その他のリソース

OUTPUT 句 (Transact-SQL)

構文

引数

@table_variable

output_table

column_list

scalar_expression

column_alias_identifier

DELETED

INSERTED

from_table_name

*

column_name

$action

解説

OUTPUT 句から返されたデータをテーブルに挿入する

Parallelism

トリガー

データ型

キュー

アクセス許可

例

A. OUTPUT INTO を INSERT ステートメントで使う

B. OUTPUT を DELETE ステートメントで使う

C. OUTPUT INTO を UPDATE ステートメントで使う

D. OUTPUT INTO を使って式を返す

E. OUTPUT INTO を UPDATE ステートメント内で from_table_name と共に使う

F. OUTPUT INTO を DELETE ステートメント内で from_table_name と共に使う

G. OUTPUT INTO をラージ オブジェクト データ型と共に使う

H. OUTPUT を INSTEAD OF トリガー内で使う

I. OUTPUT INTO を ID 列および計算列と共に使う

J. OUTPUT と OUTPUT INTO を単一のステートメント内で使う

K. OUTPUT 句から返されたデータを挿入する

次の手順

フィードバック

フィードバック

その他のリソース

`*`

G. OUTPUT INTO をラージオブジェクトデータ型と共に使う