Articles

Using INSERT OUTPUT in a SQL Server Transaction

10月 12, 2021 by admin

By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. アンディ-ウォーレンは彼のために最もよく働いたものがのあるポインターを与えるために経験の彼の多くの年を共有し、いかにこの知識のいくつかを利

Problem

頻繁に、トランザクション内でラップされたセットベースの操作でテーブルにレコードを挿入する必要がある状況で、最初のINSERTコマンドの結果であったキー値を渡す必要がある関連テーブルに後続の挿入をスポーンオフする必要があります。 Sql ServerのTransact/SQL拡張のおかげで、これははるかに簡単になり、単一のステートメントで実行できます。.. 引き金なし！

ソリューション

Microsoft SQL ServerのTransact/SQLの機能強化の1つは、INSERTステートメントのOUTPUTサブ句です。 INSERTステートメントを使用して挿入されたレコードをキャプチャできるようになりました(新しい行のID列値もキャプチャできると思います)。なぜトリガーを使用しないのですか？

これは、SQL Serverの実行可能で実績のある構造ですよね？短い答えは”はい、それはあります。”しかし、トリガーは、データベースが保持するそれらの厄介な小さな秘密の一つです。彼らはちょうどあなたに右に飛び出すと言う”ここに私はありません！たとえば、デッドロックのトラブルシューティングプロセスや、パフォーマンスの低いクエリのチューニングを考えてみましょう。バックグラウンドで実行されるトリガーが問題の原因になっている可能性がありますが、ストアドプロシージャの検索や、アドホックなT/SQLコードの検索を繰り返してから、診断しようとしているものに付随するデータ変更言語コマンド(DML)を起動するトリガーがあると考えるのをやめる前に、おそらく停止します。トリガーの使用と、アプリケーションのコードスタックで使用されるアドホックなT/SQLコードの使用とを関連付け、sql Serverインスタンスに渡して、処理プラクティスを敬遠します。それが私がINSERT-OUTPUT構造体で見るものが好きな理由です。挿入された値をキャプチャしてセカンダリコマンドに渡すことができるという利点があり、これをすべて原子性のために単一のトランザクション内にラップすることができます。この構成の構文は以下に示されており、基本的なINSERT T/SQLコマンドとはわずかに異なります:

INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column>-およびSOME_TABLEから他の列が必要な場合はSOME_OTHER_TABLEに
>
(
column_list>
)
SELECT
(
column_list>
)
SELECT
(
column_list>
>

)
from source_table_or_JOIN_of_multiple_TABLES>
where filtering_criteria>

これと標準のinsertステートメントの唯一の違いは、出力。..文に。これを簡単にするために、仮想化されたINSERTテーブル（トリガーが使用するのと同じテーブル）の値をキャプチャして別のテーブルへの2次INSERTを処理する元のINSERT文の中で、単に2次INSERT文と考えることができます。以下の例では、ホリデーシーズンに合わせて、企業のAdventureWorksオフィスで少しの雇用を担当しているとしましょう。右陽気な古いエルフは、いくつかの店内のプロモーションのために雇われており、企業の方針に沿って、あなたは常に新しい雇用のための90日間のレビ私たちは、人事の一部に追加の作業をせずに、新しい雇用が入力されたときにnotficationを記録したいと考えています。以下のコードは、INSERT-OUTPUTを使用してこれを行う方法を示しています。

USE AdventureWorks;
GO
---サンプルテーブルを作成します
/*
注、これは完全に正規化されていません。 これが実際の解決策であれば、通知タイプの別のテーブル
を含めていました。また、Varchar（xx）NotificationType列の代わりに、Notificationtypeid列をNotificationsテーブルに使用します。
*/
スキーマ承認dboの作成;
移動
テーブルを作成します。
(
IDENTITY(1,1)NOT NULL,
VARCHAR(30)NOT NULL,
VARCHAR(30)NOT NULL,
制約主キークラスタ化
(
ASC
)ON
)ON;
テーブルを作成します。
(
IDENTITY(1,1)NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR(30)NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
)ON;

この小さな練習のためにオブジェクトを構築したので、INSERT-OUTPUT構造を実際に見ることができます。..

/*
Staffに追加されたキー値を挿入する方法を示します。StaffID
通知に。単一トランザクションのStaffID
*/
INSERT INTO HR.Staff(FirstName,LastName)
出力が挿入されました。StaffID,DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES('Santa','Claus');

StaffテーブルとNotificationテーブルの両方から今すぐ選択すると、キー値が両方のテーブルに正常に入力されたことがわかります。

select*from hr.staff;
select*FROM HR.Notification;

Microsoft SQL ServerのTransact/SQLの機能強化は、INSERTステートメントのOUTPUTサブ句です

今、INSERT-OUTPUTを使用することに非常に重要であり、非常に制限的な注意点があ出力ターゲットを外部キー関係の一部にすることはできません。データベース内のその関係を介して他のオブジェクトへのカスケード関係がない場合でも。それがある場合に何が起こるかを見てみましょう。 StaffidのNotificationに外部キーを追加し、StaffidテーブルのStaffID列を参照してから、休日のヘルプを追加しようとします:

--StaffID列の外部キーを通知テーブルに追加
ALTER TABLE HR.Notification ADD CONSTRAINT
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);
/*
staffに追加されたキー値を挿入する方法を示します。StaffID
通知に。単一トランザクションのStaffID
*/
INSERT INTO HR.Staff(FirstName,LastName)
出力が挿入されました。StaffID,DATEADD(d,90,GETDATE()),'90日間のレビュー'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES('Frosty','Snowman');
SELECT*FROM HR.Staff;
SELECT*FROM HR.Notification;

次のエラーメッセージが期待どおりに返されます。

Msg332、レベル16、状態1、行17
出力INTO句のターゲットテーブル'HR.Notification'は、（主キー、外部キー） 参照制約'Fk_Notification_Staff'が見つかりました。チャンスは良いですので、これはおそらく、この場合には良いです氏雪だるまは90日で周りになるつもりはありません。
Next Steps

OUTPUT句についての詳細を読む
また、OUTPUT句についてのこの前のヒントを読む
著者からのより多くのヒントは、このリンクを

最終更新：2010-12-13









著者について

Mex Alex

Using INSERT OUTPUT in a SQL Server Transaction

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Problem

ソリューション

Next Steps

著者について

著者について

著者について

著者について

著者について

h5>

tim fordはmindbodyのシニアデータベース管理者です。
すべての私のヒントを表示

関連リソース

より多くのデータベース開発者のヒント。..

コメントを残すコメントをキャンセル

Using INSERT OUTPUT in a SQL Server Transaction

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Problem

ソリューション

Next Steps

著者について

著者について

著者について

著者について

著者について

h5> tim fordはmindbodyのシニアデータベース管理者です。すべての私のヒントを表示 関連リソース より多くのデータベース開発者のヒント。..

コメントを残す コメントをキャンセル

You may like

あなたの読書を追跡するための三つの最良の方法

あなたの前部負荷洗濯機ドライブベルトを取り替える方法

h5>

tim fordはmindbodyのシニアデータベース管理者です。
すべての私のヒントを表示

関連リソース

より多くのデータベース開発者のヒント。..

コメントを残すコメントをキャンセル