无物理表场景下，如何用临时表实现SQL记录变更追踪？

一、识别变更记录的方法 #

要找出CODE发生变化的记录，核心逻辑是将当前数据集与上一次运行的历史数据集按CENTRE和Fromdate做关联，对比两者的CODE值是否存在差异。

假设我们用临时存储保存历史状态，每次运行查询时：

1. 关联当前表与历史表，筛选CODE不一致的行；
2. 若需追踪新增记录，可同时纳入历史表中不存在的行。

示例SQL逻辑：

-- 筛选变更/新增记录
SELECT 
    curr.CENTRE,
    curr.CODE AS New_CODE,
    hist.CODE AS Old_CODE,
    curr.Fromdate
FROM #Raw_Table curr
LEFT JOIN ##History_Table hist 
    ON curr.CENTRE = hist.CENTRE 
    AND curr.Fromdate = hist.Fromdate
WHERE 
    -- 已存在记录的CODE变更
    curr.CODE <> hist.CODE 
    -- 新增记录（可选，根据需求调整）
    OR hist.CENTRE IS NULL;

若仅关注已有记录的CODE变更，删除OR hist.CENTRE IS NULL条件即可。

二、无物理表时保存历史数据的方案 #

无法使用物理表的情况下，可选择以下临时存储方式：

1. 全局临时表（##Global_Temp） #

全局临时表对所有SQL Server会话可见，直到创建它的会话关闭且无其他会话引用，适合跨会话保存历史状态：

• 第一次运行时初始化历史表：

IF OBJECT_ID('tempdb..##History_Table') IS NULL
BEGIN
    SELECT CENTRE, CODE, Fromdate
    INTO ##History_Table
    FROM #Raw_Table;
END

• 后续运行时，先对比变更，再更新历史表：

-- 删除历史表中与当前表重复的记录
DELETE hist
FROM ##History_Table hist
JOIN #Raw_Table curr 
    ON hist.CENTRE = curr.CENTRE 
    AND hist.Fromdate = curr.Fromdate;

-- 插入当前表的最新数据
INSERT INTO ##History_Table (CENTRE, CODE, Fromdate)
SELECT CENTRE, CODE, Fromdate
FROM #Raw_Table;

注意：服务器重启或所有引用会话关闭后，全局临时表会自动删除，需考虑该场景的容灾。

2. 表变量（@History_Table） #

表变量仅在当前会话和批处理中有效，适合单次会话内周期性运行查询的场景，无法跨会话保存历史：

-- 声明表变量
DECLARE @History_Table TABLE (CENTRE INT, CODE INT, Fromdate DATE);

-- 第一次初始化
IF NOT EXISTS (SELECT 1 FROM @History_Table)
BEGIN
    INSERT INTO @History_Table
    SELECT CENTRE, CODE, Fromdate FROM #Raw_Table;
END

-- 查询变更记录
SELECT 
    curr.CENTRE,
    curr.CODE AS New_CODE,
    hist.CODE AS Old_CODE,
    curr.Fromdate
FROM #Raw_Table curr
LEFT JOIN @History_Table hist 
    ON curr.CENTRE = hist.CENTRE 
    AND curr.Fromdate = hist.Fromdate
WHERE curr.CODE <> hist.CODE OR hist.CENTRE IS NULL;

-- 更新表变量为最新状态
DELETE FROM @History_Table;
INSERT INTO @History_Table SELECT CENTRE, CODE, Fromdate FROM #Raw_Table;

3. 内存优化临时表（SQL Server 2014+） #

若版本支持，内存优化临时表性能更优，会话关闭后自动删除：

-- 创建内存优化临时表
CREATE TABLE ##MemHistory_Table (
    CENTRE INT NOT NULL,
    CODE INT NOT NULL,
    Fromdate DATE NOT NULL,
    PRIMARY KEY NONCLUSTERED (CENTRE, Fromdate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

-- 后续操作同全局临时表

三、完整运行脚本示例（全局临时表版） #

-- 1. 初始化历史表（首次运行）
IF OBJECT_ID('tempdb..##History_Table') IS NULL
BEGIN
    SELECT CENTRE, CODE, Fromdate
    INTO ##History_Table
    FROM #Raw_Table;
    -- 首次运行无变更，输出空结果
    SELECT * FROM (SELECT NULL AS CENTRE, NULL AS New_CODE, NULL AS Old_CODE, NULL AS Fromdate) t WHERE 1=0;
END
ELSE
BEGIN
    -- 2. 输出变更记录
    SELECT 
        curr.CENTRE,
        curr.CODE AS New_CODE,
        hist.CODE AS Old_CODE,
        curr.Fromdate
    FROM #Raw_Table curr
    LEFT JOIN ##History_Table hist 
        ON curr.CENTRE = hist.CENTRE 
        AND curr.Fromdate = hist.Fromdate
    WHERE 
        curr.CODE <> hist.CODE 
        OR hist.CENTRE IS NULL;

    -- 3. 更新历史表为最新状态
    DELETE hist
    FROM ##History_Table hist
    JOIN #Raw_Table curr 
        ON hist.CENTRE = curr.CENTRE 
        AND hist.Fromdate = curr.Fromdate;

    INSERT INTO ##History_Table (CENTRE, CODE, Fromdate)
    SELECT CENTRE, CODE, Fromdate
    FROM #Raw_Table;
END

按此脚本运行，8/17查询时会自动输出CENTRE=7918中Fromdate为21/07/2023（CODE从6变2）和26/07/2022（CODE从6变3）的两条变更记录。

内容的提问来源于stack exchange，提问作者Soben_SPDEV