Articles

UPDATEステートメントのOUTPUT句

by admin

OUTPUT句は、SQL Server2005バージョンで導入されました。 OUTPUT句は、INSERT文、UPDATE文、またはDELETE文の影響を受けた各行の値を返します。 SQL Server2008で導入されたMERGEステートメントもサポートしています。 OUTPUT句の結果は、クエリの実行中に別のテーブルに挿入できます。 この句は、監査目的で最も一般的に使用されます。 これを使用することで、誰かがCDCのように動作するカスタムプロセスを実装するか、CDCと同じように動作することができます。

さらに、OUTPUT句の結果は、確認メッセージ、ロギング、その他のアプリケーション要件などで使用するために処理アプリケーションに返すことができます。 この記事では、更新された行の結果をUPDATE文のテーブル変数に取り込む際に、OUTPUT句を使用する例のセットを提供します。以下の例に従うには、SQL Serverデータベースで以下のT-SQLコードを実行して、テーブルDepartment_Srcを作成する必要があります。 参照されるセクションにはOutput_Updateが含まれています。以下の例を試してみるためのT-SQLコードを含むsqlファイル。下の画像に示すように、レコードがテーブルに挿入されたことをテーブルデータで確認できます。

UPDATEステートメントでOUTPUT句を実装

前の記事で説明したように、OUTPUT句は二つの仮想テーブル（マジックテーブル）にアクセスできます。

  1. “INSERTED”には新しい行（挿入または更新の行）が含まれています。set)
  2. “deleted”行の古いコピーが含まれています(updateのセット)

UPDATE文を持つOUTPUT句は、挿入された内部テーブルと削除された内部テーブルの両方にアクセスできます。 つまり、UPDATE文を実行すると同時に、これらの両方の表にアクセスできることを意味します。 たとえば、テーブル内のいくつかの列を1つの行に対して更新しているとします。 新しく更新された値は、テーブルへの変更であり、挿入された内部テーブル内にあります。 さらに、変更されたデータは古いデータ値であり、削除された内部テーブルに挿入されます。

更新値の表示

OUTPUT句を使用して、挿入された接頭辞を持つ列名を選択するか、INSERTEDを使用して、更新された値を出力ウィンドウに表示できます。*挿入されたテーブルのすべての列を表示します。 さらに、DELETED接頭辞を持つ列名を選択するか、DELETEDを使用して、テーブルの古いデータ値を出力ウィンドウに表示することができます。*そのテーブルのすべての列を表示します。 これは、management studioウィンドウに出力メッセージの一部を印刷するのにも役立ちます。これを例で説明するために、以下のコードを実行しています。GroupName列を「Research And Development」値から「Engineering」部門の「IT」値に更新しようとしています。上記のコードを実行すると、下の画像に示すようにOUTPUT句の結果が表示されます。

テーブル変数への出力結果の挿入

この例を紹介するために、@Updatedというテーブル変数を宣言して、OUTPUT句の結果をこのテーブル変数 結果をテーブル変数に格納するために、OUTPUT INTO構文を使用します。 このアプローチでは、クエリによって更新された新しい値と古い値の両方を行に設定することができ、同じバッチ内の次のステップに使用できます。上記のコードを実行すると、下の画像に示すように、結果が@Updatedテーブル変数に挿入されていることがわかります。

OUTPUT句の使用は、タイプ3のゆっくりと変化する次元をロードするために

上記の例では、OUTPUT句は、タイプ3の次元テーブルをロード これを例で説明するために、Department_Type3というタイプ3次元のテーブルを作成する以下のコードを実行しています。上記のコードを実行すると、下の画像に示すようにテーブルデータの結果を見ることができます。

---Type 3 table exampleIF OBJECT_ID ('Department_Type3', 'U') IS NOT NULL DROP TABLE dbo.Department_Type3;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NULL, varchar(50) NULL, varchar(50) NULL, NULL) ON GO---Insert some test valuesInsert into .(,,) Values('Engineering','Research and Development',getdate());Select * from .

Department_Type3テーブルに新しいレコードを挿入すると、Groupname_Old列にNULL値があり、Groupname_Currentに最近の挿入の値があることがわかります。 「エンジニアリング」部門名のGroupname_Currentに変更があり、Department_Type3テーブルを新しいGroupname_Current値で更新する必要があると仮定します。 これはTYPE3ディメンションテーブルなので、ここでの要件は、Groupname_Old列を”Research and Development”で更新し、Groupname_Currentを新しい値で更新することです。

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', EffectiveDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted. as ,inserted.GroupName_current as GroupName_new, deleted.EffectiveDate as ModifiedDate_old,inserted.EffectiveDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Update the GroupName_old with old valuesUpdate aSet a.GroupName_old=b.GroupName_old, a.EffectiveDate=b.ModifiedDate_newfrom as ainner join @Updated as bon a.DepartmentID=b.DepartmentID--Querying the final tableSelect * from

上記のコードを実行すると、削除された内部テーブルの結果を@Updatedテーブル変数にキャプチャし、そのデータを使用してこのタイプ3を実装す 下の画像に示すように、上記のステートメントの実行前後の最終テーブルデータの結果を見ることができます。

UPDATE文の実行前

UPDATE文の実行後

OUTPUT句の制限

OUTPUT句がサポートされていないシナリオの一部:

  1. DML文は、

    1. OUTPUT句がサポートされていないシナリオの一部:

      1. ローカルパーティションビュー、分散パーティションビュー、またはリモートテーブル。
      2. EXECUTEステートメントを含むINSERTステートメント。
      3. データベースの互換性レベルが100に設定されている場合、OUTPUT句ではフルテキスト述語は使用できません。OUTPUT INTO句を使用して、ビューまたは行セット関数に挿入することはできません。
      4. ターゲットとしてテーブルを持つOUTPUT INTO句が含まれている場合、ユーザー定義関数を作成できません

      完全なリストは次のリンクにリストされています。https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

      Summary

      上記の例に示すように、OUTPUT句は使いやすく、クエリの実行中に古い値と新しい値の両方で更新された値の出力結果をテーブル変数にキャプ