无增量列时如何每日增量加载SQL表数据至目标库并执行存储过程

一、带修改日期字段的6张表：基于时间戳的增量同步 #

• 先在Staging库创建同步控制表，记录每张表的上次同步时间：

  CREATE TABLE SyncControl (
      TableName VARCHAR(100) PRIMARY KEY,
      LastSyncTime DATETIME2 NOT NULL DEFAULT '1900-01-01'
  )
  -- 初始化6张表的同步时间
  INSERT INTO SyncControl (TableName)
  SELECT 'Table1' UNION SELECT 'Table2' -- 替换为实际表名
  

• SSIS同步逻辑：
  1. 读取对应表的LastSyncTime作为起始时间
  2. 从生产库提取增量数据：

     SELECT * 
     FROM LiveDB.dbo.[目标表名] 
     WHERE ModifiedDate > (SELECT LastSyncTime FROM SyncControl WHERE TableName = '[目标表名]')
     

  3. 将增量数据同步到Staging表（通过主键判断：存在则更新，不存在则插入）
  4. 同步完成后更新控制表时间：

     UPDATE SyncControl 
     SET LastSyncTime = GETDATE() 
     WHERE TableName = '[目标表名]'
     

• 注意：处理ModifiedDate的时区、精度问题，避免漏抓或重复抓取数据

二、无增量标识的19张表：按需选择以下方案 #

方案1：基于哈希值的增量对比（无需修改生产库） #

• 对生产表和Staging表的记录生成唯一哈希值，通过对比识别变更：
  1. 在生产库临时计算记录哈希值（处理NULL值避免错误）：

     SELECT 
         ID, -- 主键
         HASHBYTES('SHA2_256', CONCAT(ISNULL(Col1, ''), ISNULL(Col2, ''), ...)) AS RecordHash
     INTO #LiveHashes
     FROM LiveDB.dbo.[目标表名]
     

  2. 对比筛选变更：
     • 新增：#LiveHashes存在但Staging表不存在的ID
     • 更新：ID存在但哈希值不一致的记录
     • 删除：Staging表存在但#LiveHashes不存在的ID
  3. 同步对应变更到Staging表
• 优化：大表分批次计算哈希值，避免内存占用过高

方案2：启用生产库变更数据捕获（CDC）（精准高效） #

• 在生产库开启目标表的CDC功能，自动记录所有增删改操作：

  -- 开启数据库CDC
  EXEC sys.sp_cdc_enable_db
  -- 开启目标表CDC
  EXEC sys.sp_cdc_enable_table
      @source_schema = N'dbo',
      @source_name = N'[目标表名]',
      @role_name = NULL
  

• SSIS中直接读取CDC日志表（cdc.dbo_[目标表名]_CT），提取上次同步后的变更数据同步到Staging库
• 优势：精准捕获增量，性能远优于全量对比；劣势：需要生产库权限，部分环境可能受限

方案3：分批次全量对比（适合中等规模表） #

• 按主键分批次遍历生产表和Staging表，逐批次对比记录差异：
  1. 确定主键范围（比如ID从1到10000、10001到20000等）
  2. 每次同步一个批次的生产数据，与Staging同批次数据对比，只同步有差异的记录
• 优势：无需修改生产库；劣势：性能略低于CDC，适合无法启用CDC的场景

方案4：生产表添加增量标识（长期最优解） #

• 申请在生产表新增LastModifiedDate字段，并创建更新触发器：

  -- 添加字段
  ALTER TABLE LiveDB.dbo.[目标表名] ADD LastModifiedDate DATETIME2 NOT NULL DEFAULT GETDATE()
  -- 创建触发器，更新时自动刷新时间
  CREATE TRIGGER trg_UpdateLastModified
  ON LiveDB.dbo.[目标表名]
  AFTER UPDATE
  AS
  BEGIN
      UPDATE t
      SET LastModifiedDate = GETDATE()
      FROM LiveDB.dbo.[目标表名] t
      INNER JOIN inserted i ON t.ID = i.ID
  END
  

• 之后即可复用第一部分的时间戳增量同步方案，这是最稳定高效的长期方案

三、整体执行流程 #

1. 在SSIS中按表的大小/同步效率排序，优先执行大表的增量同步任务
2. 设置任务依赖：确保所有Staging表同步完成后，再执行复制的存储过程
3. 新增同步日志表，记录每张表的同步时间、行数、状态，方便排查问题：

   CREATE TABLE SyncLog (
       LogID INT IDENTITY(1,1) PRIMARY KEY,
       TableName VARCHAR(100) NOT NULL,
       SyncStartTime DATETIME2 NOT NULL,
       SyncEndTime DATETIME2,
       RowsSynced INT,
       Status VARCHAR(20) NOT NULL -- 成功/失败
   )
   

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