如何将现有非时态表转换为Temporal Table并保留历史数据？

一、解决转时态表的报错问题 #

你遇到的报错有两个核心原因：一是现有记录的ValidTo未设为datetime2类型的最大值，二是时态表必须有主键，而你的UserId存在重复无法直接做主键。以下是保留所有历史数据的分步解决方法：

1. 修正ValidTo字段值
   时态表要求当前表（非历史表）的所有记录ValidTo必须等于datetime2的最大值，代表这些是当前有效的记录。执行更新语句：

   UPDATE dbo.Wage
   SET ValidTo = '9999-12-31 23:59:59.9999999';
   

2. 添加唯一主键
   由于UserId重复，无法直接用它做主键，最简便的方式是新增自增主键列：

   ALTER TABLE dbo.Wage
   ADD WageId INT IDENTITY(1,1) PRIMARY KEY;
   

   如果你有其他能唯一标识每条记录的字段组合（比如UserId+ValidFrom），也可以用复合主键替代自增列：

   ALTER TABLE dbo.Wage
   ADD PRIMARY KEY (UserId, ValidFrom);
   

3. 启用SYSTEM_TIME周期及时态表功能
   先添加时间周期，再开启系统版本控制（系统会自动创建历史表，也可以手动指定历史表名）：

   -- 添加SYSTEM_TIME周期
   ALTER TABLE dbo.Wage
   ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
   
   -- 开启系统版本控制，自动创建历史表dbo.WageHistory
   ALTER TABLE dbo.Wage
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WageHistory));
   

二、能否向时态表插入自定义ValidFrom/ValidTo值？ #

可以，但需要遵守时态表的版本控制规则：

• 默认开启系统版本控制后，SQL Server会自动维护ValidFrom和ValidTo的值（ValidFrom取事务开始时间，ValidTo设为最大值）。
• 若要插入自定义时间的历史数据，需先关闭系统版本控制，插入完成后再重新开启：

  -- 关闭系统版本控制
  ALTER TABLE dbo.Wage
  SET (SYSTEM_VERSIONING = OFF);
  
  -- 向历史表插入自定义时间的历史记录
  INSERT INTO dbo.WageHistory (UserId, WageAmount, ValidFrom, ValidTo)
  VALUES (1, 5000, '2023-01-01 00:00:00.0000000', '2023-06-01 00:00:00.0000000');
  
  -- 若要向当前表插入自定义ValidFrom的有效记录，需保证ValidTo为最大值
  INSERT INTO dbo.Wage (UserId, WageAmount, ValidFrom, ValidTo)
  VALUES (1, 6000, '2023-06-01 00:00:00.0000000', '9999-12-31 23:59:59.9999999');
  
  -- 重新开启系统版本控制
  ALTER TABLE dbo.Wage
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WageHistory));
  

• 注意：插入时必须保证ValidFrom < ValidTo，且当前表的ValidTo必须是最大值，历史表的ValidTo不能是最大值，否则会触发约束报错。

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