如何修改时态表正常表的时间戳列？跨库复制需保留原时间戳

问题说明 #

使用SQL Server系统版本控制（时态表）时，ValidFrom（行开始）、ValidTo（行结束）这类时间戳列的修改存在限制：关闭系统版本控制后，历史表的时间戳可以修改（只要符合时间序列规则），但直接修改当前表的时间戳会报错：

The GENERATED ALWAYS columns in the "dbo.Customer" table cannot be updated.

核心需求是：将一批时态表1:1复制到另一个数据库，同时保留原库的时态表/历史表时间戳。

原因 #

当前表的ValidFrom、ValidTo是GENERATED ALWAYS类型列，即使关闭系统版本控制，这个“强制自动生成”的属性依然存在，所以无法直接更新。而历史表没有这个属性限制，因此可以修改。

解决方法 #

方案1：临时移除GENERATED ALWAYS属性修改当前表 #

如果只是单表修改时间戳，可按以下步骤操作：

-- 1. 关闭系统版本控制
ALTER TABLE dbo.[Customer] SET (SYSTEM_VERSIONING = OFF);

-- 2. 移除时间戳列的GENERATED ALWAYS属性，转为普通datetime2列
ALTER TABLE dbo.[Customer] ALTER COLUMN ValidFrom DATETIME2 NOT NULL;
ALTER TABLE dbo.[Customer] ALTER COLUMN ValidTo DATETIME2 NOT NULL;

-- 3. 修改目标时间戳值
UPDATE dbo.[Customer] SET ValidFrom = '2024-03-25 11:21:38.1854297' WHERE [Id] = 5;

-- 4. 重新添加时态属性并关联历史表，开启版本控制
ALTER TABLE dbo.[Customer] 
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE dbo.[Customer] 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[CustomerHistory]));

方案2：批量跨库迁移完整保留时间戳 #

适合批量迁移场景，步骤更高效：

1. 在目标库创建普通表：复制源库时态表的结构，但去掉PERIOD FOR SYSTEM_TIME定义和GENERATED ALWAYS属性，只保留普通的datetime2类型时间戳列，同时创建对应的历史表结构。
2. 全量导入数据：用跨库查询、SSIS、bcp等工具，将源库时态表和历史表的所有数据导入目标库的普通表中。
3. 启用系统版本控制：为目标库的表添加时态属性，关联历史表并开启版本控制。

示例代码：

-- 目标库创建普通表（示例）
CREATE TABLE dbo.[Customer] (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);

CREATE TABLE dbo.[CustomerHistory] (
    Id INT NOT NULL,
    Name NVARCHAR(50) NOT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);

-- 跨库导入数据（假设源库名为SourceDB）
INSERT INTO dbo.[Customer] SELECT * FROM SourceDB.dbo.[Customer];
INSERT INTO dbo.[CustomerHistory] SELECT * FROM SourceDB.dbo.[CustomerHistory];

-- 添加时态属性并启用版本控制
ALTER TABLE dbo.[Customer] 
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE dbo.[Customer] 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[CustomerHistory]));

注意事项 #

• 操作前务必备份数据，避免修改或迁移过程中出现数据丢失。
• 重新启用系统版本控制前，要确保当前表和历史表的时间戳符合时态表一致性规则：当前行的ValidTo必须是最大值（如9999-12-31 23:59:59.9999999），历史记录的时间序列不能重叠，上一条的ValidTo必须等于下一条的ValidFrom。
• 批量操作时，建议编写循环脚本处理所有目标表，提高效率。

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