UPDATE OUTPUT句の代替方法－ SQL Serverで変数に結果を格納

2024-05-17

SQL ServerにおけるUPDATE OUTPUTを使用した変数への格納

SQL ServerのUPDATEステートメントには、OUTPUT句と呼ばれるオプション機能があります。この機能を使用すると、更新操作の影響を受けた行のデータを、変数に格納することができます。これにより、更新されたデータにアクセスしたり、ログ記録や監査目的で使用したりすることが可能になります。

構文

UPDATE table_name
   SET column1 = value1,
       column2 = value2
   WHERE condition
   OUTPUT (
       column1,
       column2,
       ...
   )
   INTO @variable1,
       @variable2,
       ...;

説明

table_name: 更新対象のテーブル名
column1, column2: 更新する列名
value1, value2: 更新後の値
condition: 更新対象の行を絞り込む条件
@variable1, @variable2: 更新されたデータ格納用の変数

例

UPDATE Customers
   SET LastName = 'Smith',
       FirstName = 'John'
   WHERE CustomerID = 123
   OUTPUT (
       CustomerID,
       LastName,
       FirstName
   )
   INTO @customerID,
       @lastName,
       @firstName;

SELECT @customerID, @lastName, @firstName;

この例では、CustomersテーブルのCustomerID 123のレコードのLastNameとFirstNameを更新し、更新されたデータを変数@customerID、@lastName、@firstNameに格納します。その後、これらの変数の値をSELECTステートメントで選択しています。

補足

OUTPUT句は、INSERT、UPDATE、DELETEステートメントで使用できます。
OUTPUT句で指定できる列は、更新対象の列だけでなく、その他の列も指定できます。
変数は、@記号で始まる必要があります。
変数のデータ型は、OUTPUT句で指定した列のデータ型と一致する必要があります。

利点

更新されたデータに簡単にアクセスできる
ログ記録や監査目的に役立つ
トリガーやストアドプロシージャで使用できる

注意点

OUTPUT句を使用すると、パフォーマンスが低下する可能性がある
OUTPUT句は、行バージョン列を含むことはできません

CREATE TABLE Customers (
    CustomerID INT IDENTITY PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50) NOT NULL
);

INSERT INTO Customers (LastName, FirstName)
VALUES ('Jones', 'David'),
       ('Williams', 'Mary'),
       ('Brown', 'Robert');

DECLARE @log_file NVARCHAR(255) = 'C:\CustomersLog.txt';

UPDATE Customers
   SET LastName = 'Johnson',
       FirstName = 'Peter'
   WHERE CustomerID = 2
   OUTPUT (
       CustomerID,
       OriginalLastName = LastName,
       OriginalFirstName = FirstName,
       NewLastName = 'Johnson',
       NewFirstName = 'Peter'
   )
   INTO @updatedCustomer;

-- ログファイルに記録
OPEN @log_file = READWRITE;
WRITE @log_file;
WRITE @log_file, 'Customer updated: ' + CONVERT(NVARCHAR(10), @updatedCustomer.CustomerID);
WRITE @log_file, 'Original name: ' + @updatedCustomer.OriginalLastName + ', ' + @updatedCustomer.OriginalFirstName;
WRITE @log_file, 'New name: ' + @updatedCustomer.NewLastName + ', ' + @updatedCustomer.NewFirstName;
CLOSE @log_file;

SELECT * FROM Customers;

Customersテーブルを作成します。
ログファイルへのパスを@log_file変数に格納します。
CustomerID 2の顧客情報をJohnson、Peterに変更します。
OUTPUT句を使用して、更新された顧客情報を変数@updatedCustomerに格納します。
ログファイルを開き、更新された顧客情報とログメッセージを書き込みます。
ログファイルを閉じます。

このサンプルコードは、UPDATE OUTPUTを使用した変数への格納と、ログ記録の例をわかりやすく示しています。実際の運用では、必要に応じて修正や拡張を行ってください。

UPDATE OUTPUT 以外の代替方法

SELECT ステートメント

DECLARE @updatedCustomerID INT;
DECLARE @updatedLastName VARCHAR(50);
DECLARE @updatedFirstName VARCHAR(50);

UPDATE Customers
   SET LastName = 'Smith',
       FirstName = 'John'
   WHERE CustomerID = 123;

SELECT @updatedCustomerID = CustomerID,
       @updatedLastName = LastName,
       @updatedFirstName = FirstName
FROM Customers
WHERE CustomerID = 123;

SELECT @updatedCustomerID, @updatedLastName, @updatedFirstName;

長所

シンプルでわかりやすい構文
OUTPUT 句よりもパフォーマンスが優れている場合がある

短所

2回クエリを実行する必要があるため、処理時間が長くなる可能性がある
ロックの問題が発生する可能性がある

INSERT INTO ... SELECT ステートメント

DECLARE @updatedCustomerID INT;
DECLARE @updatedLastName VARCHAR(50);
DECLARE @updatedFirstName VARCHAR(50);

INSERT INTO #temp_table (CustomerID, LastName, FirstName)
SELECT CustomerID, LastName, FirstName
FROM Customers
WHERE CustomerID = 123;

UPDATE Customers
   SET LastName = 'Smith',
       FirstName = 'John'
   WHERE CustomerID = 123;

SELECT TOP 1 *
FROM #temp_table
INTO @updatedCustomerID,
      @updatedLastName,
      @updatedFirstName;

DROP TABLE #temp_table;

SELECT @updatedCustomerID, @updatedLastName, @updatedFirstName;

ロックの問題が発生しにくい

#temp_table などの臨時テーブルを作成する必要がある
複雑な構文になる可能性がある

動的 SQL

DECLARE @sql NVARCHAR(MAX);
DECLARE @updatedCustomerID INT;
DECLARE @updatedLastName VARCHAR(50);
DECLARE @updatedFirstName VARCHAR(50);

SET @sql = N'
UPDATE Customers
   SET LastName = ''Smith'',
       FirstName = ''John''
   WHERE CustomerID = 123;

SELECT CustomerID, LastName, FirstName
INTO @updatedCustomerID,
      @updatedLastName,
      @updatedFirstName
FROM Customers
WHERE CustomerID = 123;
';

EXEC sp_executesql @sql;

SELECT @updatedCustomerID, @updatedLastName, @updatedFirstName;

柔軟性が高く、複雑な更新操作にも対応できる

セキュリティ上のリスクがある

トランザクショントリガー

更新操作が行われた際に自動的にトリガーが実行され、更新されたデータをログテーブルなどに記録することができます。

コードを記述する必要がなく、メンテナンスが容易
常に最新の情報が記録される

トリガーの作成と設定が必要
パフォーマンスに影響を与える可能性がある

どの方法を選択するかは、状況によって異なります。シンプルな更新操作であれば、SELECTステートメントやINSERT INTO ... SELECTステートメントが適しています。処理速度が重要であれば、UPDATE OUTPUTよりもこれらの方法の方が優れている場合もあります。複雑な更新操作や常に最新の情報が必要な場合は、動的SQLやトランザクショントリガーを検討することができます。

sql-server