MENU

SQL ServerのMERGE(UPSERT)でソフトデリート更新を監査テーブルに記録する方法（OUTPUT句活用）

Microsoft

SQL Server の MERGE（UPSERT）で「Source には存在しないが Target には存在する ID」を検出してソフトデリート（例：RecordStatusKey=2）に更新したい場面はよくあります。さらに実務では、その更新対象（どの ID がソフトデリートになったか）を監査（Audit）テーブルへ確実に記録したいはずです。結論から言うと、MERGE の OUTPUT 句を使えば、更新された行を一度受けてから監査テーブルへ安全に INSERT できます。

ITtripTOOLS
その「面倒」、無料で解決。 ITtripがつくる、6つの道具。 無料ツール一覧へ すべて無料・追加課金なし
先頭0を守るCSV編集こわさないCSV PDFに直接書き込みかけるPDF 写真＋説明をPDFに写レポ スマホ⇄PCで共有コピペ便 · 文章・写真 みんなの日程を調整日程Fit 匿名質問・投票を集めるKOEQ
目次

MERGE でソフトデリートした行を監査テーブルに記録する最短ルート

やりたいことを整理すると、次の 2 つです。

  • Source に無い ID を Target で見つけたら、物理削除ではなく RecordStatusKey を 2 に更新する
  • その更新で対象になった ID を 監査テーブルに INSERTして履歴を残す

MERGE の強みは、INSERT/UPDATE/DELETE の結果を OUTPUT 句でまとめて取り出せる点です。取り出した結果をテーブル変数や一時表に入れておけば、後段で自由に監査テーブルへ流し込めます。

まずは結論の SQL（OUTPUT を受けて監査へ INSERT）

質問の意図に沿って、更新前（DELETED）と更新後（INSERTED）を出力し、本当にソフトデリートに変わった行だけを監査へ入れる形です。

-- 監査したい MERGE 結果を受ける（小規模ならテーブル変数でもOK）
DECLARE @MergeOutputTable TABLE
(
    [Action] varchar(10) NOT NULL,
    [Id] bigint NOT NULL,
    OldRecordStatusKey int NULL,
    NewRecordStatusKey int NULL
);

MERGE INTO dbo.TargetTbl AS T
USING dbo.SourceTbl AS S
ON T.Id = S.Id
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.RecordStatusKey = 2
OUTPUT
    $action,
    INSERTED.Id,
    DELETED.RecordStatusKey,
    INSERTED.RecordStatusKey
INTO @MergeOutputTable;

-- 監査テーブルへ（ソフトデリートになったものだけ）
INSERT INTO Audit.TrackALLDeletions (Id, DateOfDeletion)
SELECT
    Id,
    SYSDATETIME()
FROM @MergeOutputTable
WHERE [Action] = 'UPDATE'
  AND NewRecordStatusKey = 2
  AND (OldRecordStatusKey IS NULL OR OldRecordStatusKey <> 2);

このパターンが強い理由はシンプルで、MERGE 内で INSERT を頑張らなくても、OUTPUT の結果を「証跡の材料」として保持できるからです。MERGE は 1 ステートメントで複数種別の処理を行うため、処理直後に「何が起きたか」を取り出せる OUTPUT が実務では最重要になります。

OUTPUT 句で「何が取れるのか」を理解しておく

OUTPUT 句で取り出せる情報は、監査用途にかなり向いています。特に、更新前後の値を比較できるのが大きいです。

出力要素意味監査での使いどころ
$actionMERGE が行った操作（INSERT/UPDATE/DELETE）「ソフトデリート更新」だけ抽出するフィルタに使う
INSERTED.列処理後の行（UPDATE/INSERT 後の値）更新後の RecordStatusKey が 2 かどうか判定
DELETED.列処理前の行（UPDATE/DELETE 前の値）更新前が 2 だった行を除外し「本当に変化した」ものだけ監査

質問文にある OUTPUT $action, INSERTED.Id の時点で、更新対象 ID は既に取れています。そこに加えて DELETED/INSERTED の RecordStatusKey を出力すると、「2 に変わった更新」だけを正確に判別できます。

「ソフトデリートの更新だけ」を取りこぼさない条件設計

監査で怖いのは、次のようなケースです。

  • すでに RecordStatusKey=2 の行に対して、また 2 をセットしてしまい、監査が二重に増える
  • ソフトデリート以外の UPDATE も混ざり、監査の意味が薄くなる

これを避けるために、監査 INSERT 側で Old≠New を見るのが堅いです。今回なら次の条件が要点です。

  • [Action] = 'UPDATE'（MERGE による更新だけ）
  • NewRecordStatusKey = 2（ソフトデリートに更新された）
  • OldRecordStatusKey <> 2（もともと 2 ではなかった）

この 3 点で、「ソフトデリートに変わった更新」だけを監査テーブルへ送れます。

監査テーブル設計：最低限と、実務で効く拡張

監査テーブルは「後で調べられる」ことがすべてなので、ID と日時だけでは足りなくなることがあります。運用でありがちな追加要件を織り込んだ例を示します。

シンプルな監査テーブル例

CREATE SCHEMA Audit;
GO

CREATE TABLE Audit.TrackALLDeletions
(
AuditId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
Id bigint NOT NULL,
DateOfDeletion datetime2(7) NOT NULL,
OldRecordStatusKey int NULL,
NewRecordStatusKey int NULL,
ActionType varchar(10) NOT NULL,
BatchId uniqueidentifier NOT NULL,
DeletedBy sysname NULL
);

CREATE INDEX IX_TrackALLDeletions_Id_Date
ON Audit.TrackALLDeletions (Id, DateOfDeletion);

ポイントは以下です。

  • Old/New を持つ：調査で「なぜ消えた？」に答えやすい
  • BatchId を持つ：バッチ単位で追跡でき、障害解析が速い
  • DeletedBy（任意）：アプリケーションユーザーや実行ログインを入れる余地

MERGE と監査 INSERT を同一トランザクションにまとめる

監査は「更新は成功したのに監査 INSERT が失敗した」を避けたいので、可能なら同一トランザクションで扱うのが無難です。

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @BatchId uniqueidentifier = NEWID();

DECLARE @MergeOutputTable TABLE
(
    [Action] varchar(10) NOT NULL,
    [Id] bigint NOT NULL,
    OldRecordStatusKey int NULL,
    NewRecordStatusKey int NULL
);

BEGIN TRY
    BEGIN TRAN;

    MERGE INTO dbo.TargetTbl AS T
    USING dbo.SourceTbl AS S
    ON T.Id = S.Id
    WHEN NOT MATCHED BY SOURCE THEN
        UPDATE SET T.RecordStatusKey = 2
    OUTPUT
        $action,
        INSERTED.Id,
        DELETED.RecordStatusKey,
        INSERTED.RecordStatusKey
    INTO @MergeOutputTable;

    INSERT INTO Audit.TrackALLDeletions
    (
        Id, DateOfDeletion, OldRecordStatusKey, NewRecordStatusKey,
        ActionType, BatchId, DeletedBy
    )
    SELECT
        Id,
        SYSDATETIME(),
        OldRecordStatusKey,
        NewRecordStatusKey,
        [Action],
        @BatchId,
        ORIGINAL_LOGIN()
    FROM @MergeOutputTable
    WHERE [Action] = 'UPDATE'
      AND NewRecordStatusKey = 2
      AND (OldRecordStatusKey IS NULL OR OldRecordStatusKey <> 2);

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

XACT_ABORT ON を入れておくと、実務でありがちな「途中でエラーになったのに半端に進む」を減らせます（ただし、業務要件に応じて TRY/CATCH のログ戦略は調整してください）。

テーブル変数 vs 一時表：量が増えると差が出る

OUTPUT の受け先はテーブル変数でも一時表（#temp）でも構いませんが、件数やチューニング要件で選ぶと安定します。

観点テーブル変数一時表（#temp）
少量データ扱いやすいやや冗長
大量データプラン最適化が外れやすい（推定件数の問題が出やすい）統計情報が使え、プランが安定しやすい
インデックス制約・インデックスの自由度は限定的必要ならインデックス作成が容易
運用上の扱いスコープが明確で安全トラブル調査で中身を確認しやすい

バッチで数万〜数十万行の可能性があるなら、最初から一時表を採用しておくと後がラクです。

#temp を使う例

CREATE TABLE #MergeOutput
(
    [Action] varchar(10) NOT NULL,
    [Id] bigint NOT NULL,
    OldRecordStatusKey int NULL,
    NewRecordStatusKey int NULL
);

MERGE INTO dbo.TargetTbl AS T
USING dbo.SourceTbl AS S
ON T.Id = S.Id
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET T.RecordStatusKey = 2
OUTPUT
$action,
INSERTED.Id,
DELETED.RecordStatusKey,
INSERTED.RecordStatusKey
INTO #MergeOutput;

INSERT INTO Audit.TrackALLDeletions (Id, DateOfDeletion)
SELECT Id, SYSDATETIME()
FROM #MergeOutput
WHERE [Action] = 'UPDATE'
AND NewRecordStatusKey = 2
AND (OldRecordStatusKey IS NULL OR OldRecordStatusKey <> 2);

よくある落とし穴：MERGE を運用で安定させるチェックポイント

MERGE は便利ですが、現場で事故が起きやすいポイントもあります。ソフトデリート監査を確実にするために、最低限次を押さえておくと安心です。

Source 側の重複で「同一行が複数回マッチ」しないようにする

ON T.Id = S.Id のキーが Source で重複していると、期待通りに動かない・エラーになる・監査が増えるなどの原因になります。バッチ投入前に、Source を一意化したビューや CTE を噛ませるのが定番です。

WITH S AS
(
    SELECT Id
    FROM dbo.SourceTbl
    GROUP BY Id
)
MERGE INTO dbo.TargetTbl AS T
USING S
ON T.Id = S.Id
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.RecordStatusKey = 2
OUTPUT $action, INSERTED.Id, DELETED.RecordStatusKey, INSERTED.RecordStatusKey
INTO #MergeOutput;

「更新が必要なときだけ UPDATE」する発想を持つ

監査の精度は OUTPUT 側で担保できますが、無駄な更新が多いとロックやログが増えて運用コストになります。ソフトデリート対象が多い場合は、Target 側条件で不要更新を避けるのも効きます。

MERGE INTO dbo.TargetTbl AS T
USING dbo.SourceTbl AS S
ON T.Id = S.Id
WHEN NOT MATCHED BY SOURCE
     AND (T.RecordStatusKey IS NULL OR T.RecordStatusKey <> 2) THEN
    UPDATE SET T.RecordStatusKey = 2
OUTPUT $action, INSERTED.Id, DELETED.RecordStatusKey, INSERTED.RecordStatusKey
INTO #MergeOutput;

これで「すでに 2 の行をまた 2 にする」更新が減り、監査も静かになります（監査側の条件と二重で守るとさらに安心です）。

同時実行があるならロック戦略も検討する

同一 Target を複数プロセスが同時に MERGE する運用だと、タイミング次第で期待とズレることがあります。要件によっては、ターゲット側にロックヒントを付ける、バッチを単一実行にする、または分割キーで競合しない設計に寄せるなどの工夫が必要です。

例として、ターゲットにホールド系のヒントを付けて整合性を寄せる書き方があります（ただし、環境の同時実行性や待ちの増加とトレードオフなので、負荷試験の上で採用してください）。

MERGE INTO dbo.TargetTbl WITH (HOLDLOCK) AS T
USING dbo.SourceTbl AS S
ON T.Id = S.Id
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.RecordStatusKey = 2
OUTPUT $action, INSERTED.Id, DELETED.RecordStatusKey, INSERTED.RecordStatusKey
INTO #MergeOutput;

監査テーブルに「何を残すべきか」現場目線の考え方

監査テーブルは増え続けるのが前提なので、残す情報は最小限にしつつ、調査に耐える設計が重要です。おすすめは「キーと理由が追える」構成です。

  • 対象キー（Id）：必須
  • 発生日時：必須（できれば datetime2 または datetimeoffset）
  • 操作種別：UPDATE/INSERT 等を保持すると横展開しやすい
  • 更新前後の最小差分：今回なら RecordStatusKey の Old/New が最小構成
  • バッチ識別子：障害解析で効く（BatchId、ジョブ実行IDなど）
  • 実行主体：ORIGINAL_LOGIN() やアプリユーザー名など（入れるなら意味を統一）

逆に、Target の全カラムを監査にコピーすると容量が急増し、検索性も落ちます。必要なら「別テーブルでスナップショットを持つ」「重要列だけ持つ」「一定期間だけ保持してアーカイブする」など、運用戦略とセットで考えるのが現実的です。

MERGE 以外の方法（比較して選べるようにする）

要件によっては、MERGE にこだわらず「UPDATE + INSERT（OUTPUT）」の方が読みやすく保守しやすいケースもあります。監査目的に限れば、OUTPUT は UPDATE 単体でも使えます。

UPDATE でソフトデリートし、OUTPUT で監査に直接流す

「Source に無い Target」を UPDATE する処理は、NOT EXISTS で書けます。MERGE を避けたい運用や、ソフトデリートだけ別バッチにしたい場合に有効です。

UPDATE T
SET T.RecordStatusKey = 2
OUTPUT
    INSERTED.Id,
    SYSDATETIME()
INTO Audit.TrackALLDeletions (Id, DateOfDeletion)
FROM dbo.TargetTbl AS T
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.SourceTbl AS S
    WHERE S.Id = T.Id
)
AND (T.RecordStatusKey IS NULL OR T.RecordStatusKey <> 2);

この形だと「結果を受けてから INSERT」という二段構えすら不要で、監査に直接 OUTPUT できます。監査項目を増やしたい場合は、DELETED/INSERTED を OUTPUT に追加して監査テーブル列に合わせます。

トリガー、テンポラル、CDC/CT との比較

監査は「アプリ改修が難しい」「すべての更新を漏れなく取りたい」要件が出ることがあります。その場合の選択肢を整理します。

方式長所短所向いている状況
MERGE + OUTPUT処理結果を確実に取得でき、監査の粒度を自由に設計できるSQL の設計が複雑になりやすい、運用で注意点があるバッチ処理で意図した更新だけ監査したい
UPDATE/INSERT + OUTPUT読みやすく、監査に直接流せるUPSERT 全体を一文にまとめたい場合は分割が必要監査対象が明確（ソフトデリートだけ等）
トリガーアプリ側を触らずに全更新を拾える予期せぬ副作用、性能劣化、デバッグ難全アプリ共通で監査が必要、統制強め
システム バージョン管理（テンポラル）履歴が自動で残り、復元・追跡が強い設計変更が必要、履歴量が増える行の履歴を丸ごと追いたい（監査・復旧）
CDC/Change Tracking変更検出に強い（ETL/同期向け）用途が監査そのものとはズレることがあるデータ同期・差分連携が主目的

「MERGE でソフトデリートし、その結果を監査に残したい」という要件には、今回の OUTPUT を受けて INSERT 方式がもっとも直球です。一方で、ソフトデリートだけなら UPDATE + OUTPUT 直書きが運用しやすいことも多いので、システムの事情に合わせて選ぶと失敗しにくいです。

実装をより堅牢にする小技集

監査データの重複を DB 側で防ぐ

何らかの理由で同じ ID のソフトデリートが同一バッチ内で二重に入るのが嫌なら、監査テーブル側に制約やユニークキーを置く手もあります。たとえば「同一 Id に対し、同一 BatchId では 1 回だけ」など。

CREATE UNIQUE INDEX UX_TrackALLDeletions_Id_Batch
ON Audit.TrackALLDeletions (Id, BatchId);

これで、監査側の安全柵が増えます（ただし要件次第では「同じバッチで複数回あり得る」場合もあるので、定義は慎重に）。

日時は UTC を選ぶと後で楽になることが多い

複数リージョンやログ統合がある環境なら、SYSUTCDATETIME() を使うとタイムゾーン問題が減ります。国内限定でも、将来の拡張を考えると検討価値があります。

SELECT SYSUTCDATETIME();

監査テーブルは肥大化するので、保持戦略を決める

監査は増え続けます。運用で詰みやすいポイントなので、次のどれかは早めに決めておくと安全です。

  • 保持期間を決めて古い監査をアーカイブ（別 DB/別ストレージ）
  • 月次・週次でパーティションを切る（大量件数向け）
  • 参照頻度の高い検索キー（Id、Date）にインデックスを付ける

まとめ：MERGE の OUTPUT を「監査の入り口」にする

Source に無い ID を Target でソフトデリートし、その更新対象を監査テーブルに記録することは、SQL Server の MERGE で十分実現できます。ポイントは、MERGE の中で直接監査 INSERT を頑張るのではなく、OUTPUT 句で結果（$action と INSERTED/DELETED）を受けてから、後段で監査へ INSERTする構成にすることです。さらに 更新前後の RecordStatusKey を出しておけば、「本当に値が変わったソフトデリート」だけを抽出でき、監査の精度が一段上がります。

まずは今回の基本形（OUTPUT → テーブル変数/一時表 → 監査 INSERT）を採用し、データ量や運用の同時実行性に応じて「一時表化」「トランザクション化」「不要更新の抑制」「監査テーブルの保持戦略」へ拡張していくのが、実務で失敗しにくい進め方です。

Microsoft
SQL Server 監査 MERGE OUTPUT ソフトデリート

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次