SQL Server实现SCD Type 2的MERGE语句偶发生成重复行问题咨询

代码存在的明显问题及修改方案： #

问题1：临时表设计不符合业务键要求

你当前的维度行唯一业务标识是meterkey + MeterSerialNumber组合，但临时表#meterkeysinsert仅存储了MeterKey单字段，后续二次插入时仅用MeterKey关联源表，会导致同一个MeterKey下所有序列号的行都被误插入。

问题2：源表未做去重处理

如果dbo.test中存在同一组meterkey + MeterSerialNumber的重复行，MERGE匹配时会多次触发更新、插入逻辑，直接生成重复的最新行。

问题3：二次插入逻辑存在冗余风险

现有逻辑中MERGE的NOT MATCHED分支已经负责插入全新的维度行，后续单独的INSERT负责插入更新后的新维度行，但关联规则缺失业务键校验，很容易触发重复插入。

具体修改步骤：

1. 调整临时表结构，补全业务键字段

CREATE TABLE #meterkeysinsert
(
    MeterKey int,
    MeterSerialNumber VARCHAR(255), -- 请和你表中该字段的实际类型保持一致
    change VARCHAR(10)
);

2. 对源表做去重处理，保证每组业务键仅有一行最新数据参与MERGE，同时修改MERGE的OUTPUT逻辑补全业务键输出

MERGE INTO [DIM].[MeterDetails] AS Target
-- 源表先按业务键去重，取最新的一行
USING (
    SELECT * FROM (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY meterkey, MeterSerialNumber ORDER BY DateSpecifiedKey DESC) AS rn
        FROM dbo.test
    ) t WHERE rn = 1
) AS Source
ON Target.meterkey = source.meterkey
AND target.MeterSerialNumber = Source.MeterSerialNumber
AND target.islatest = 1

WHEN matched THEN
  UPDATE SET Target.islatest = 0,
             Target.todatekey = @Datenow

WHEN NOT matched BY target THEN
  INSERT (meterkey,[MeterSerialNumber],[lguf],[electricityMetertype],[profileType],[timeSwitchCode],[lineLossFactorId],[standardSettlementConfiguration],[energisationStatus],[DateSpecifiedKey],[distributorId],[gspid],[FromDatekey],[ToDatekey],[IsLatest])
  VALUES (Source.meterkey,Source.[MeterSerialNumber],Source.[lguf],Source.[electricityMetertype],Source.[profileType],Source.[timeSwitchCode],Source.[lineLossFactorId],Source.[standardSettlementConfiguration],Source.[energisationStatus],Source.[DateSpecifiedKey],Source.[distributorId],Source.[gspid],@Datenow,NULL,1)
-- 输出时补全MeterSerialNumber字段
output Source.meterkey, Source.MeterSerialNumber, $action
INTO #meterkeysinsert;

3. 调整二次插入的关联逻辑，按完整业务键关联，同时对源表做去重

INSERT INTO [DIM].[MeterDetails]
            (MeterKey,[MeterSerialNumber],[lguf],[electricityMetertype],[profileType],[timeSwitchCode],[lineLossFactorId],[standardSettlementConfiguration],[energisationStatus],[DateSpecifiedKey],[distributorId],[gspid],[FromDateKey],[ToDateKey],[IsLatest])
SELECT A.MeterKey
,[MeterSerialNumber]
,[lguf]
,[electricityMetertype]
,[profileType]
,[timeSwitchCode]
,[lineLossFactorId]
,[standardSettlementConfiguration]
,[energisationStatus]
,[DateSpecifiedKey]
,[distributorId]
,[gspid]
,@Datenow
,null
,1
FROM  (
    -- 源表再次去重，避免重复插入
    SELECT * FROM (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY meterkey, MeterSerialNumber ORDER BY DateSpecifiedKey DESC) AS rn
        FROM dbo.test
    ) t WHERE rn = 1
) a
INNER JOIN #meterkeysinsert CID
    ON a.MeterKey = CID.Meterkey
    -- 补全业务键关联条件
    AND a.MeterSerialNumber = CID.MeterSerialNumber
    AND CID.change = 'UPDATE'

4. 新增唯一过滤索引防止脏数据
   给目标表添加唯一过滤索引，从根本上避免同一组业务键出现多条最新行的问题：

CREATE UNIQUE NONCLUSTERED INDEX UIX_MeterDetails_LatestBusinessKey
ON DIM.MeterDetails (meterkey, MeterSerialNumber)
WHERE IsLatest = 1;

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