SQL Serverで一定件数ずつデータ更新とデータ追加をしてみた

テーブルデータを更新する際に、テーブル全件を一括更新しようとすると、テーブルロックによりデッドロックが発生する可能性があるため、一定件数(例：1000件)ずつ更新する必要がある。

また、一定件数ずつ実行するデータ更新／データ追加の処理が複数ある場合は、一時テーブルを利用するとよい。

今回は、SQL Serverで一時テーブルを利用して、一定件数ずつデータ更新とデータ追加を行うSQLを作成してみたので、その手順を共有する。

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

一定件数ずつデータ更新・データ追加するSQLの実行

一定件数ずつデータ更新・データ追加するSQLの実行手順は、以下の通り。

1) データ追加を行うためのテーブル(USER_DATA_MEMO_UPD)を作成する。

CREATE TABLE dbo.USER_DATA_MEMO_UPD (
      id INT NOT NULL
    , upd_time DATETIME2 NOT NULL
);

2) 今回は、以下のデータを利用するものとする。

SELECT * FROM dbo.USER_DATA ORDER BY id ASC

SELECT * FROM dbo.USER_DATA_MEMO_UPD ORDER BY id ASC

3) 上記USER_DATAテーブルのデータ更新と、USER_DATA_MEMO_UPDテーブルのデータ追加を行うSQLは、以下の通り。

DECLARE @RowCount INT = 1;  -- 現在の処理件数
DECLARE @BatchSize INT = 2; -- 一括処理件数(最大値：1000)
DECLARE @RoopCount INT = 1; -- ループカウント数

-- 更新前に、user_data.memoをNULLにリセットする
UPDATE dbo.USER_DATA SET memo = NULL;

-- 一時テーブルを作成する
CREATE TABLE #tmpId (
    id INT
);

WHILE (@RowCount > 0)
BEGIN
    -- memoが未設定で、@BatchSizeに設定した件数分のidを取得し、
    -- 一時テーブルに格納する
    INSERT INTO #tmpId (id)
    SELECT TOP (@BatchSize) id 
    FROM dbo.USER_DATA 
    WHERE memo IS NULL;
    
    -- 一時テーブルに格納したレコードのmemoを一括更新する
    UPDATE u
    SET u.memo = N'ループカウント数：' + CAST(@RoopCount AS NVARCHAR) 
               + N'、実行時刻：' + FORMAT(GETDATE(), 'yyyy/MM/dd HH:mm:ss')
    FROM dbo.USER_DATA u
    INNER JOIN #tmpId ti
      ON u.id = ti.id;

    -- 一時テーブルに格納したレコードの更新履歴を一括追加する
    INSERT INTO dbo.USER_DATA_MEMO_UPD (id, upd_time)
    SELECT id, GETDATE()
    FROM #tmpId;

    -- 先ほどのupdate文での処理件数を格納する
    SET @RowCount = @@ROWCOUNT;
    -- ループカウント数を更新する
    SET @RoopCount = @RoopCount + 1;
    
    -- 一時テーブルのデータを全削除する
    TRUNCATE TABLE #tmpId;
END

-- 一時テーブルを削除する
DROP TABLE #tmpId;

4) 3)のSQLを実行した結果は以下の通りで、Messagesの赤枠部分で、(@BatchSizeに指定した)2件ずつ、データ更新/追加が実行されていることが確認できる。

5) 3)のSQLを実行後、USER_DATAテーブルのmemoが、赤枠部分のように更新されていることが確認できる。

SELECT * FROM dbo.USER_DATA ORDER BY id ASC

6) 3)のSQLを実行後、USER_DATA_MEMO_UPDテーブルに、赤枠部分のようにデータ追加されていることが確認できる。

SELECT * FROM dbo.USER_DATA_MEMO_UPD ORDER BY id ASC

7) USER_DATA_MEMO_UPDテーブルのデータを削除する。

TRUNCATE TABLE dbo.USER_DATA_MEMO_UPD

8) 3)のSQLを、@BatchSizeを3に変更した上で実行した結果は、以下の通り。

SELECT * FROM dbo.USER_DATA ORDER BY id ASC

SELECT * FROM dbo.USER_DATA_MEMO_UPD ORDER BY id ASC

要点まとめ

• 一定件数ずつ実行するデータ更新／データ追加の処理が複数ある場合は、一時テーブルを利用するとよい。