OUTPUT 句 (Transact-SQL)

[アーティクル]
10/16/2024

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

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

Note

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

使用される場所:

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

引数

@table_variable

返される行を呼び出し元に返さずにテーブルに挿入する場合に、挿入先となる table 変数を指定します。 @table_variable は、 INSERT、 UPDATE、 DELETE、または MERGE ステートメントの前に宣言する必要があります。

column_list を指定しない場合は、table 変数の列の数は OUTPUT の結果セットと同じである必要があります。ただし、ID 列と計算列はスキップされるため、同じである必要はありません。 column_list を指定した場合は、省略された列は NULL 値を許容しているか、既定値が割り当てられている必要があります。

table変数の詳細については、「table」を参照してください。

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ステートメントが完了する前の値を反映します。

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)型の列を指定します。この列は、その行に対して実行されたアクションに従って、各行の 3 つの値 (INSERT、UPDATE、またはDELETE) のいずれかを返します。

解説

OUTPUT <dml_select_list>句とOUTPUT <dml_select_list> INTO { @table_variable | output_table }句は、単一のINSERT、UPDATE、DELETE、またはMERGEステートメントで定義できます。

Note

特に指定しない限り、OUTPUT 句への参照は、OUTPUT 句と OUTPUT INTO 句の両方を参照します。

OUTPUT句は、INSERTまたはUPDATE操作の後に ID または計算列の値を取得するのに役立つ場合があります。

計算列が <dml_select_list> に含まれている場合、出力テーブルまたはテーブル変数内の対応する列は計算列ではありません。新しい列の値は、ステートメントが実行された時点で計算された値を持つ列になります。

変更がテーブルに適用される順序と、出力テーブルまたはテーブル変数に行が挿入される順序は、対応する保証はありません。

パラメーターまたは変数が UPDATE ステートメントの一部として変更された場合、 OUTPUT 句は、変更された値ではなく、ステートメントが実行される前と同じパラメーターまたは変数の値を常に返します。

OUTPUTは、WHERE CURRENT OF構文を使用するカーソルに配置されたUPDATEまたはDELETEステートメントで使用できます。

OUTPUT 句は、次のステートメントではサポートされません。

ローカルパーティションビュー、分散パーティションビュー、またはリモートテーブルを参照する DML ステートメント
INSERTEXECUTE ステートメントを含むステートメント。
データベースの互換性レベルが 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 ステートメントによって生成される値は返しません。
クエリ通知では、ステートメントは 1 つのエンティティとして扱われ、作成されるメッセージの型は入れ子になった 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句を含むステートメントをトリガーの本体内で使用する場合は、テーブルエイリアスを使用して挿入および削除されたテーブルを参照し、OUTPUTに関連付けられたINSERTEDテーブルおよびDELETEDテーブルとの列参照が重複しないようにする必要があります。

INTO キーワードも指定せずに OUTPUT 句を指定した場合、DML 操作のターゲットには、指定された DML アクションに対して有効なトリガーを定義することはできません。たとえば、 OUTPUT 句が UPDATE ステートメントで定義されている場合、ターゲットテーブルで有効な UPDATE トリガーを設定することはできません。

トリガーからの結果を許可しないsp_configure オプションが設定されている場合、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

シナリオで複数のアプリケーションが 1 つのテーブルから破壊的な読み取りを実行できる場合は、UPDATEステートメントとDELETE ステートメントでREADPASTテーブルヒントを使用します。これにより、テーブル内の最初の該当レコードを別のアプリケーションが既に読み込み中である場合に発生するロックの問題が起こらなくなります。

アクセス許可

SELECT アクセス許可は、 <dml_select_list> を通じて取得された列、または <scalar_expression>で使用されるすべての列に対して必要です。

INSERT アクセス許可は、 <output_table>で指定されたすべてのテーブルで必要です。

例

この記事の Transact-SQL コードサンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトのホームページからダウンロードできます。

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 をラージオブジェクトデータ型と共に使う

次の例では、.WRITE 句を使用して、Production.Document テーブルの nvarchar(max) 列である 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

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 をラージオブジェクトデータ型と共に使う