📝 本番運用に強い SQL Server のアーカイブ処理を作る

SQL Server のアーカイブ処理は、単に古いデータを移すだけではありません。本番運用では次のような課題が必ず出てきます。

テーブルごとに SQL がバラバラ
列追加のたびにアーカイブ SQL を修正
INSERT と DELETE の件数が合わずデータ欠損
いつ・どの条件でアーカイブしたか記録がない
大量データで DELETE が重い
運用担当者が安心して実行できない

この記事では、アーカイブ処理を “仕組みとして整える” ことを目的に、実際にテーブルを作って動かしながら、

汎用アーカイブストアド
年度末 YM の計算関数
アーカイブログ
チェック処理（事前件数・件数一致・残件数）
Zaiko / Sales の実行例

までをまとめて紹介します。

すべてコピペで動くので、手を動かしながら理解できます。

1. 今回のアーカイブ要件

テーブル	アーカイブ条件	YM形式	アーカイブ先
Zaiko（在庫）	5年以上前の年度末（3月）まで	yyyymm	Zaiko_Archive
Sales（販売）	3年以上前の年度末（3月）まで	yyyymm	Sales_Archive

2. テーブル作成（Zaiko / Sales）

Zaiko（在庫）

CREATE TABLE dbo.Zaiko (
    ZaikoID   INT,
    ItemCode  VARCHAR(20),
    ItemName  VARCHAR(100),
    Quantity  INT,
    YM        CHAR(6),
    UpdatedAt DATETIME
);

Zaiko_Archive

SELECT TOP 0 * INTO dbo.Zaiko_Archive FROM dbo.Zaiko;

Sales（販売）

CREATE TABLE dbo.Sales (
    SalesID    INT, 
    ItemCode   VARCHAR(20),
    Quantity   INT,
    Price      INT,
    Amount     INT,
    YM         CHAR(6),
    SalesDate  DATE
);

Sales_Archive

SELECT TOP 0 * INTO dbo.Sales_Archive FROM dbo.Sales;

3. サンプルデータ投入（2020〜2025 の複数年）

Zaiko(在庫)

INSERT INTO dbo.Zaiko (ZaikoID, ItemCode, ItemName, Quantity, YM, UpdatedAt)
VALUES
(1,'A001','りんご',120,'202001','2020-01-05'),
(2,'A002','みかん',150,'202102','2021-02-06'),
(3,'A003','バナナ',80,'202203','2022-03-07'),
(4,'A004','ぶどう',95,'202304','2023-04-08'),
(5,'A005','メロン',60,'202405','2024-05-09'),
(6,'A006','スイカ',110,'202506','2025-06-10');

Sales（販売）

INSERT INTO dbo.Sales (ItemCode, Quantity, Price, Amount, YM, SalesDate)
VALUES
('A001', 3, 120, 360, '202001', '2020-01-05'),
('A002', 2, 150, 300, '202102', '2021-02-06'),
('A003', 5, 100, 500, '202203', '2022-03-07'),
('A004', 1, 200, 200, '202304', '2023-04-08'),
('A005', 4, 300, 1200, '202405', '2024-05-09'),
('A006', 2, 250, 500, '202506', '2025-06-10');

4. アーカイログテーブル（チェック情報つき）

CREATE TABLE dbo.ArchiveLog (
    LogID              INT IDENTITY(1,1) PRIMARY KEY,
    TableName          SYSNAME,
    ArchiveTable       SYSNAME,
    WhereClause        NVARCHAR(MAX),
    ConditionValue     NVARCHAR(MAX),
    PreCheckCount      INT,
    InsertCount        INT,
    DeleteCount        INT,
    AfterRemainCount   INT,
    StartTime          DATETIME2,
    EndTime            DATETIME2,
    DurationMs         INT,
    Status             VARCHAR(20),
    ErrorMessage       NVARCHAR(MAX)
);

5. 汎用アーカイブストアドの解説

ここが一番難しいので、丁寧に説明します。

✔ なぜ「汎用化」するのか？

アーカイブ処理はテーブルごとに SQL を書くと、次の問題が起きます。

列追加のたびに SQL 修正が必要
条件が複雑になるとミスが起きる
テーブルが増えるほど管理が破綻する

そこで テーブル名・アーカイブ先・何年前まで をパラメータ化し、どのテーブルでも同じストアドでアーカイブできるようにします。

✔ 年度末 YM 計算をストアド内に内蔵した理由

読者が関数を別で作る必要がなく、 ストアド 1 つで完結する方が圧倒的に使いやすい からです。

✔ チェック処理が重要な理由

本番運用では、次のチェックが必須です。ある程度チェックしておかないと気づいたらデータが消えてたってこともあるので・・・・。

チェック	理由
事前件数	条件ミスを防ぐ
INSERT 件数	アーカイブ漏れ防止
DELETE 件数	削除漏れ防止
残件数チェック	条件ミス・削除漏れ検知
ログ保存	監査・トラブル対応

✔ ConditionValue をログに残す理由

WhereClause だけだと 「実際にどの値で実行されたか」 がわかりません。

例： YM <= @ArchiveYM ではなく YM <= 202103 を残すことで、運用者が一目で理解できます。

6. 汎用アーカイブストアド

CREATE PROCEDURE dbo.ArchiveGeneric
    @TableName       SYSNAME,
    @ArchiveTable    SYSNAME,
    @YearsAgo        INT,            -- 何年前までアーカイブするか（例：5年）
    @YMColumn        SYSNAME = 'YM'  -- YM列名（デフォルト YM）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @PreCheckCount INT = 0;
    DECLARE @InsertCount INT = 0;
    DECLARE @DeleteCount INT = 0;
    DECLARE @AfterRemainCount INT = 0;
    DECLARE @StartTime DATETIME2 = SYSDATETIME();
    DECLARE @EndTime DATETIME2;
    DECLARE @ErrorMessage NVARCHAR(MAX) = NULL;

    ----------------------------------------
    -- ① 年度末 YM を計算（関数なし）
    ----------------------------------------
    DECLARE @BaseYear INT;

    SET @BaseYear = YEAR(GETDATE());
    IF MONTH(GETDATE()) < 4
        SET @BaseYear = @BaseYear - 1;

    DECLARE @ArchiveYM INT = (@BaseYear - @YearsAgo) * 100 + 3;

    DECLARE @WhereClause NVARCHAR(MAX) =
        QUOTENAME(@YMColumn) + ' <= ' + CAST(@ArchiveYM AS NVARCHAR(10));

    DECLARE @ConditionValue NVARCHAR(MAX) = @WhereClause;

    BEGIN TRY
        ----------------------------------------
        -- ② 事前チェック（対象件数）
        ----------------------------------------
        SET @SQL = '
            SELECT @cnt = COUNT(*)
            FROM ' + QUOTENAME(@TableName) + '
            WHERE ' + @WhereClause + ';
        ';
        EXEC sp_executesql @SQL, N'@cnt INT OUTPUT', @cnt = @PreCheckCount OUTPUT;

        ----------------------------------------
        -- ③ アーカイブ（INSERT）
        ----------------------------------------
        SET @SQL = '
            INSERT INTO ' + QUOTENAME(@ArchiveTable) + '
            SELECT *
            FROM ' + QUOTENAME(@TableName) + '
            WHERE ' + @WhereClause + ';
        ';
        EXEC(@SQL);
        SET @InsertCount = @@ROWCOUNT;

        ----------------------------------------
        -- ④ 元テーブルから削除（DELETE）
        ----------------------------------------
        SET @SQL = '
            DELETE FROM ' + QUOTENAME(@TableName) + '
            WHERE ' + @WhereClause + ';
        ';
        EXEC(@SQL);
        SET @DeleteCount = @@ROWCOUNT;

        ----------------------------------------
        -- ⑤ 件数チェック（INSERT = DELETE）
        ----------------------------------------
        IF @InsertCount <> @DeleteCount
        BEGIN
            SET @ErrorMessage = 'InsertCount と DeleteCount が一致しません。';
            THROW 50001, @ErrorMessage, 1;
        END

        ----------------------------------------
        -- ⑥ アーカイブ後の残件数チェック
        ----------------------------------------
        SET @SQL = '
            SELECT @cnt = COUNT(*)
            FROM ' + QUOTENAME(@TableName) + '
            WHERE ' + @WhereClause + ';
        ';
        EXEC sp_executesql @SQL, N'@cnt INT OUTPUT', @cnt = @AfterRemainCount OUTPUT;

        IF @AfterRemainCount <> 0
        BEGIN
            SET @ErrorMessage = '削除漏れがあります（AfterRemainCount <> 0）。';
            THROW 50002, @ErrorMessage, 1;
        END

        ----------------------------------------
        -- ⑦ ログ（成功）
        ----------------------------------------
        SET @EndTime = SYSDATETIME();

        INSERT INTO dbo.ArchiveLog
        (
            TableName, ArchiveTable, WhereClause, ConditionValue,
            PreCheckCount, InsertCount, DeleteCount, AfterRemainCount,
            StartTime, EndTime, DurationMs,
            Status, ErrorMessage
        )
        VALUES
        (
            @TableName, @ArchiveTable, @WhereClause, @ConditionValue,
            @PreCheckCount, @InsertCount, @DeleteCount, @AfterRemainCount,
            @StartTime, @EndTime,
            DATEDIFF(MILLISECOND, @StartTime, @EndTime),
            'Success', NULL
        );
    END TRY
    BEGIN CATCH
        ----------------------------------------
        -- ⑧ ログ（失敗）
        ----------------------------------------
        SET @EndTime = SYSDATETIME();
        SET @ErrorMessage = ISNULL(@ErrorMessage, ERROR_MESSAGE());

        INSERT INTO dbo.ArchiveLog
        (
            TableName, ArchiveTable, WhereClause, ConditionValue,
            PreCheckCount, InsertCount, DeleteCount, AfterRemainCount,
            StartTime, EndTime, DurationMs,
            Status, ErrorMessage
        )
        VALUES
        (
            @TableName, @ArchiveTable, @WhereClause, @ConditionValue,
            @PreCheckCount, @InsertCount, @DeleteCount, @AfterRemainCount,
            @StartTime, @EndTime,
            DATEDIFF(MILLISECOND, @StartTime, @EndTime),
            'Error', @ErrorMessage
        );

        THROW;
    END CATCH
END

7. Zaiko / Sales のアーカイブを実行してみる

Zaiko（5年）

EXEC dbo.ArchiveGeneric
    @TableName    = 'Zaiko',
    @ArchiveTable = 'Zaiko_Archive',
    @YearsAgo     = 5;

実行

対象1件が正しくアーカイブされています。ログもきれいに出力されています。

Sales（3年）

EXEC dbo.ArchiveGeneric
    @TableName    = 'Sales',
    @ArchiveTable = 'Sales_Archive',
    @YearsAgo     = 3;

対象3件が正しくアーカイブされています。ログもきれいに出力されています。

8. アーカイログを確認する

SELECT *
FROM dbo.ArchiveLog
ORDER BY LogID DESC;

例：

TableName	ConditionValue	PreCheck	Insert	Delete	Remain	Status
Zaiko	YM <= 202103	2	2	2	0	Success
Sales	YM <= 202303	3	3	3	0	Success

9. まとめ

YearsAgo を渡すだけでどのテーブルでもアーカイブ可能
SELECT * で列追加に強い
事前チェック・件数一致・残件数チェックで安全性を担保
ログで「いつ・どの条件で・何件」を完全に記録
サンプルデータ（2020〜2025）で動作がわかりやすい

本番運用に耐えるアーカイブ処理として、そのまま導入できます。