如何在SQL Server中实现SCD TYPE 2表：阈值变更时更新插入记录

我们需要维护一个支持**缓慢变化维度类型2（SCD Type 2）**的表，核心规则是：当指定Band的阈值（Value）发生变化时，为该Band插入新的版本记录，并将旧版本记录的结束日期标记为当前生效日的前一天；如果阈值无变化，则不做任何修改。目标效果是：Junior Band的旧记录更新结束日期，同时插入新阈值记录；Senior Band因阈值未变，保持原有记录不变。

1. 定义表结构与模拟数据 #

假设初始维度表为EmployeeBandThresholds，结构如下：

CREATE TABLE EmployeeBandThresholds (
    BandName VARCHAR(50) NOT NULL,
    ThresholdValue DECIMAL(10,2) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    IsCurrent BIT NOT NULL DEFAULT 1,
    PRIMARY KEY (BandName, StartDate) -- 复合主键：Band+生效日期唯一标识版本
);

模拟初始数据：

INSERT INTO EmployeeBandThresholds (BandName, ThresholdValue, StartDate, EndDate)
VALUES 
    ('Junior', 50000.00, '2023-01-01', '9999-12-31'),
    ('Senior', 80000.00, '2023-01-01', '9999-12-31');

模拟待更新的新阈值数据（用临时表存储）：

CREATE TABLE #NewThresholds (
    BandName VARCHAR(50) NOT NULL,
    ThresholdValue DECIMAL(10,2) NOT NULL
);

INSERT INTO #NewThresholds (BandName, ThresholdValue)
VALUES 
    ('Junior', 55000.00), -- 阈值变化，需生成新版本
    ('Senior', 80000.00); -- 阈值未变，无需修改

2. 实现SCD Type 2逻辑 #

步骤1：标记旧版本为非当前 #

对阈值发生变化的Band，更新其当前生效记录的结束日期为新记录生效日的前一天，并标记为非当前：

UPDATE ebt
SET 
    EndDate = DATEADD(DAY, -1, CAST(GETDATE() AS DATE)),
    IsCurrent = 0
FROM EmployeeBandThresholds ebt
JOIN #NewThresholds nt ON ebt.BandName = nt.BandName
WHERE ebt.IsCurrent = 1
AND ebt.ThresholdValue != nt.ThresholdValue;

步骤2：插入新版本记录 #

为阈值变化的Band插入新的生效记录，设置当前日期为生效起始日，最远未来日期为结束日（表示当前生效）：

INSERT INTO EmployeeBandThresholds (BandName, ThresholdValue, StartDate, EndDate, IsCurrent)
SELECT 
    nt.BandName,
    nt.ThresholdValue,
    CAST(GETDATE() AS DATE),
    '9999-12-31',
    1
FROM #NewThresholds nt
JOIN EmployeeBandThresholds ebt ON nt.BandName = ebt.BandName
WHERE ebt.IsCurrent = 0 -- 仅插入刚被标记为非当前的Band
AND ebt.ThresholdValue != nt.ThresholdValue;

可选：用MERGE合并操作（单语句框架） #

如果希望用单条语句完成更新+插入（需额外补充新记录插入）：

MERGE INTO EmployeeBandThresholds ebt
USING (
    SELECT 
        BandName, 
        ThresholdValue,
        CAST(GETDATE() AS DATE) AS NewStartDate,
        DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS OldEndDate
    FROM #NewThresholds
) nt ON ebt.BandName = nt.BandName AND ebt.IsCurrent = 1
WHEN MATCHED AND ebt.ThresholdValue != nt.ThresholdValue THEN
    UPDATE SET 
        EndDate = nt.OldEndDate,
        IsCurrent = 0
WHEN NOT MATCHED THEN -- 处理首次新增的Band
    INSERT (BandName, ThresholdValue, StartDate, EndDate, IsCurrent)
    VALUES (nt.BandName, nt.ThresholdValue, nt.NewStartDate, '9999-12-31', 1);

-- 插入新版本记录（MERGE无法直接生成新的当前版本，需单独执行）
INSERT INTO EmployeeBandThresholds (BandName, ThresholdValue, StartDate, EndDate, IsCurrent)
SELECT 
    nt.BandName,
    nt.ThresholdValue,
    nt.NewStartDate,
    '9999-12-31',
    1
FROM (
    SELECT 
        BandName, 
        ThresholdValue,
        CAST(GETDATE() AS DATE) AS NewStartDate
    FROM #NewThresholds
) nt
JOIN EmployeeBandThresholds ebt ON nt.BandName = ebt.BandName
WHERE ebt.IsCurrent = 0
AND ebt.ThresholdValue != nt.ThresholdValue;

3. 验证结果 #

执行操作后查询表数据：

SELECT * FROM EmployeeBandThresholds ORDER BY BandName, StartDate;

预期输出：

• Junior Band有两条记录：旧记录EndDate为当前日期前一天、IsCurrent=0；新记录StartDate为当前日期、EndDate='9999-12-31'、IsCurrent=1。
• Senior Band仅保留初始记录，IsCurrent=1，无任何修改。

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