SQL Serverで安全かつ高速にデータパッチを適用する方法【テンプレート付き】

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF OBJECT_ID('tempdb..#BeforeTable') IS NOT NULL DROP TABLE #BeforeTable;
IF OBJECT_ID('tempdb..#AfterTable') IS NOT NULL DROP TABLE #AfterTable;

BEGIN TRANSACTION;

SELECT * INTO #BeforeTable FROM 対象テーブル;

SELECT '修正前' AS 区分, * FROM 対象テーブル WHERE 条件 = 'xxx';

UPDATE 対象テーブル
SET 修正列 = 'xxx'
WHERE 条件 = 'xxx';

SELECT '修正後' AS 区分, * FROM 対象テーブル WHERE 条件 = 'xxx';

SELECT * INTO #AfterTable FROM 対象テーブル;

SELECT '差分' AS 区分, * FROM #AfterTable
EXCEPT
SELECT '差分', * FROM #BeforeTable;

SELECT COUNT(*) AS 差分件数
FROM (
    SELECT * FROM #AfterTable
    EXCEPT
    SELECT * FROM #BeforeTable
) AS 差分;

ROLLBACK TRANSACTION;

IF OBJECT_ID('tempdb..#BeforeTable') IS NOT NULL DROP TABLE #BeforeTable;
IF OBJECT_ID('tempdb..#AfterTable') IS NOT NULL DROP TABLE #AfterTable;

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF OBJECT_ID('tempdb..#BeforeTable') IS NOT NULL DROP TABLE #BeforeTable;
IF OBJECT_ID('tempdb..#AfterTable') IS NOT NULL DROP TABLE #AfterTable;

BEGIN TRANSACTION;

SELECT * INTO #BeforeTable FROM 対象テーブル;
SELECT * FROM 対象テーブル WHERE 条件 = 'xxx';

INSERT INTO 対象テーブル (列1, 列2)
VALUES ('値1', '値2');

SELECT * INTO #AfterTable FROM 対象テーブル;
SELECT * FROM 対象テーブル WHERE 条件 = 'xxx';

SELECT '追加された行' AS 区分, * FROM #AfterTable
EXCEPT
SELECT '追加された行', * FROM #BeforeTable;

SELECT COUNT(*) AS 追加件数
FROM (
    SELECT * FROM #AfterTable
    EXCEPT
    SELECT * FROM #BeforeTable
) AS 差分;

ROLLBACK TRANSACTION;

IF OBJECT_ID('tempdb..#BeforeTable') IS NOT NULL DROP TABLE #BeforeTable;
IF OBJECT_ID('tempdb..#AfterTable') IS NOT NULL DROP TABLE #AfterTable;

-- 変更前の状態を保存
SELECT * INTO #BeforeTable FROM 対象テーブル WHERE 条件;

-- トランザクション開始
BEGIN TRANSACTION;

-- パッチ処理（UPDATEやINSERT）
UPDATE 対象テーブル SET 修正列 = 'xxx' WHERE 条件 = 'xxx';

-- 変更後の状態を保存
SELECT * INTO #AfterTable FROM 対象テーブル WHERE 条件;

-- 差分抽出
SELECT * FROM #AfterTable EXCEPT SELECT * FROM #BeforeTable;

-- 差分件数確認
SELECT COUNT(*) FROM (
    SELECT * FROM #AfterTable
    EXCEPT
    SELECT * FROM #BeforeTable
) AS 差分;

-- 仮実行の終了（本番反映する場合は COMMIT）
ROLLBACK TRANSACTION;