SQL Server增量同步：如何用Merge处理API端删除的记录？

你的核心问题是当前Merge逻辑仅针对staging_table中存在的transaction_id范围操作，无法覆盖API端已删除、且不在本次staging_table中的记录。以下是几种可行的解决方案，适配不同的API返回能力：

方案1：全量同步（API返回所有未删除数据） #

如果API能够返回所有未被删除的全量数据，可以直接去掉CTE限制，对整个final_table执行Merge操作，利用WHEN NOT MATCHED BY SOURCE THEN DELETE自动清理不在staging_table中的记录（即API已删除的记录）。

优化后的SQL #

MERGE final_table AS trg
USING staging_table AS src 
    ON trg.transaction_id = src.transaction_id

-- 插入staging有但final没有的新记录
WHEN NOT MATCHED BY TARGET THEN
    INSERT (practice_id, [其他字段])
    VALUES (src.practice_id, src.[其他字段])
                
-- 更新字段有变化的匹配记录
WHEN MATCHED AND (
    ISNULL(trg.practice_id, '') != ISNULL(src.practice_id, '')
    -- 逐一添加其他需要对比的字段，避免无意义更新
) THEN 
    UPDATE SET 
        trg.practice_id = src.practice_id,
        trg.[其他字段] = src.[其他字段],
        trg.last_sync_time = GETDATE()

-- 删除final有但staging没有的记录（API已删除）
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

性能注意事项 #

• 确保final_table的transaction_id是主键或唯一非聚集索引，避免Merge时全表扫描，提升2000万条数据的匹配效率。
• 每次同步前确认staging_table确实是全量未删除数据，否则会误删正常记录。

方案2：增量同步+删除标记（API返回删除标记） #

如果API仅返回增量数据，但会给已删除的记录添加标记（比如is_deleted=1），可以在staging_table中新增is_deleted字段，针对性处理删除操作。

步骤1：修改staging_table结构 #

ALTER TABLE staging_table ADD is_deleted BIT DEFAULT 0;

优化后的Merge SQL #

MERGE final_table AS trg
USING staging_table AS src 
    ON trg.transaction_id = src.transaction_id

-- 仅插入未标记删除的新记录
WHEN NOT MATCHED BY TARGET AND src.is_deleted = 0 THEN
    INSERT (practice_id, [其他字段])
    VALUES (src.practice_id, src.[其他字段])
                
-- 匹配到标记删除的记录，直接删除
WHEN MATCHED AND src.is_deleted = 1 THEN
    DELETE

-- 更新未标记删除且字段有变化的记录
WHEN MATCHED AND src.is_deleted = 0 AND (
    ISNULL(trg.practice_id, '') != ISNULL(src.practice_id, '')
    -- 其他字段对比
) THEN 
    UPDATE SET 
        trg.practice_id = src.practice_id,
        trg.[其他字段] = src.[其他字段],
        trg.last_sync_time = GETDATE();

方案3：同步日志追踪（API无删除反馈） #

如果API既不返回全量数据，也不标记删除，只能通过追踪记录的同步出现情况来判断是否删除。

步骤1：创建同步日志表 #

CREATE TABLE sync_transaction_log (
    log_id INT IDENTITY(1,1) PRIMARY KEY,
    sync_time DATETIME DEFAULT GETDATE(),
    transaction_id VARCHAR(50) -- 适配你的transaction_id数据类型
);

步骤2：每次同步前记录本次staging的transaction_id #

INSERT INTO sync_transaction_log (transaction_id)
SELECT transaction_id FROM staging_table;

步骤3：清理长期未出现的记录 #

根据业务规则，删除连续N次同步都未出现的记录（比如7天未出现则判定为已删除）：

DELETE FROM final_table
WHERE transaction_id NOT IN (
    SELECT DISTINCT transaction_id 
    FROM sync_transaction_log 
    WHERE sync_time >= DATEADD(DAY, -7, GETDATE())
);

维护注意事项 #

• 定期清理sync_transaction_log中的旧数据，避免日志表过大影响查询效率。
• 调整时间窗口（比如7天）时，需结合业务实际的API数据更新频率。

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