今日は、変更ステートメント (INSERT、UPDATE、DELETE) からテーブル変数にデータを返すことができる SQL Server 2005 の出力句について調べました。結果を含む DML の有用なシナリオには、パージとアーカイブ、メッセージング アプリケーション、その他のシナリオが含まれます。この新しい OUTPUT 句の構文は次のとおりです。
OUTPUT <dml_select_list> INTO @table_variable は、
トリガーにアクセスするのと同様の方法で、挿入されたテーブルと削除されたテーブルを参照することによって、変更された行の古い/新しいイメージにアクセスできます。 INSERT ステートメントでは、挿入されたテーブルのみにアクセスできます。 DELETE ステートメントでは、削除されたテーブルのみにアクセスできます。 UPDATE ステートメントでは、挿入されたテーブルと削除されたテーブルにアクセスできます。以下は MSDN の例です。
結果を含む DML に役立つパージおよびアーカイブ スキームの例として、大規模な Orders テーブルがあり、履歴データを定期的にパージしたいとします。また、パージされたデータを OrdersArchive という名前のアーカイブ テーブルにコピーする必要もあります。 @DeletedOrders という名前のテーブル変数を宣言し、履歴データ (2003 年より古い注文など) をまとめて削除するループに入ります。ここで追加されたコードは OUTPUT 句で、削除されたすべての行のすべての属性を @DeletedOrders テーブル変数にコピーし、INSERT INTO ステートメントを使用してテーブル変数内のすべての行を OrdersArchive テーブルにコピーします。
DECLARE @DeletedOrders TABLE
(
注文ID INT、
注文日 DATETIME、
empidINT、
custid VARCHAR(5)、
数量INT
)
1=1の間
始める
トランスの開始
注文から先頭(5000)を削除
OUTPUT が削除されました。* INTO @DeletedOrders
WHERE 注文日 < '20030101'
注文アーカイブに挿入
SELECT * FROM @DeletedOrders
コミットトラン
@DeletedOrders から削除
IF @@rowcount < 5000
壊す
し
ます。 テーブル Employee(EmpID intidentity(1, 1) 制約 Employee_pk 主キー, FirstName varchar(100), LastName Varchar(100), Sex char(1),性別 ='M' の場合は 'Mr.'、性別 ='F' の場合は 'Ms.' の場合、給与金額、控除は給与 <3000 の場合 .20 * 給与が 30000 ～ 50000 の場合then .26* Salary (給与が 50001 ～ 75000 の場合) then .30*Salaryelse .35*Salary end、DOJ 日時制約 DOJ_DEF デフォルト getdate())
非常に単純なテーブル。case 句を使用してそれぞれ挨拶、控除、法定などを設定します。次のように、別のストアド プロシージャを作成できます。 Create プロシージャemployee_insert@Fname varchar(100),@Lname varchar(100),@sex char(1),@Salary Moneyasdeclare @empoutput table (EmpID int, FirstName varchar(100) 、LastName Varchar(100),Sex char(1), Salutation char(3),Salary Money,Deductions Money,DOJ datetime )insert into Employee(FirstName,LastName,Salary,sex)OUTPUT selected.* into @empoutputvalues(@Fname ,@Lname ,@Salary,@sex )@empoutput から * を選択
次に、ストアド プロシージャを呼び出すときに、次のように USE [MyDatabase] を使用できます。
GOexec 従業員_挿入 '美穂','吉川','F',146000
このようにして、実行中にデータが従業員テーブルに挿入され、一時変数 empoutput がテーブルの形式で定義されることに注意してください。ここで分かりやすいようにフォームで定義すると、employee テーブルに挿入されたデータが同時に empoutput 変数に出力されます。このストアド プロシージャを実行すると、次の結果が得られます。
EmpID、名、姓、性別、挨拶、給与、控除、司法省
7,美穂,吉川,女史,146000.00,51100.00,2006-04-09 23:16:44.920
、レコードは実際に従業員テーブルに挿入されます
ソースを
見ることができます
: jackyrong BLOG