SQL Server：能否选择性覆盖计算列或IDENTITY列？迁移数据咨询

一、迁移现有数据时手动指定IDENTITY列（保留自增功能） #

SQL Server的IDENTITY列默认不允许手动赋值，但可以通过临时开启IDENTITY_INSERT开关完成现有数据迁移，关闭开关后依然保持自动递增特性。

表结构创建（带计算列） #

如果long_id是entry_num与component的固定组合，直接用计算列即可，无需手动赋值，系统会自动生成并维护唯一约束：

CREATE TABLE your_table (
    entry_num INT IDENTITY(1,1) PRIMARY KEY, -- 自增主键，起始值1，步长1
    component INT FOREIGN KEY REFERENCES component_table(component_id), -- 关联外键表
    long_id AS CONCAT(entry_num, '_', component) UNIQUE -- 自动组合的计算列，加唯一约束
);

迁移现有数据 #

-- 开启手动插入IDENTITY列的权限
SET IDENTITY_INSERT your_table ON;

-- 插入现有数据，手动指定entry_num和component，long_id会自动计算生成
INSERT INTO your_table (entry_num, component)
VALUES (1001, 1), (1002, 2), (1003, 1);

-- 关闭权限，恢复自动递增
SET IDENTITY_INSERT your_table OFF;

后续新增数据 #

无需指定entry_num，系统会自动基于当前最大的entry_num值递增：

INSERT INTO your_table (component) VALUES (3); -- entry_num自动生成1004，long_id自动为'1004_3'

二、特殊场景：需要手动指定long_id（非计算列） #

如果现有数据的long_id不是简单的entry_num+component组合，必须手动赋值，可将long_id改为普通列，后续新增用触发器自动生成：

调整表结构 #

CREATE TABLE your_table (
    entry_num INT IDENTITY(1,1) PRIMARY KEY,
    component INT FOREIGN KEY REFERENCES component_table(component_id),
    long_id VARCHAR(50) UNIQUE -- 普通列，加唯一约束
);

迁移现有数据 #

SET IDENTITY_INSERT your_table ON;
-- 手动指定entry_num、component和long_id
INSERT INTO your_table (entry_num, component, long_id)
VALUES (1001, 1, 'CUST_1001_001'), (1002, 2, 'CUST_1002_002');
SET IDENTITY_INSERT your_table OFF;

新增数据自动生成long_id #

创建触发器，在新增数据时自动生成符合规则的long_id：

CREATE TRIGGER trg_auto_generate_long_id
ON your_table
AFTER INSERT
AS
BEGIN
    UPDATE your_table
    SET long_id = CONCAT('CUST_', entry_num, '_', RIGHT('000' + CAST(component AS VARCHAR), 3))
    WHERE entry_num IN (SELECT entry_num FROM inserted);
END;

-- 测试新增
INSERT INTO your_table (component) VALUES (3); -- long_id自动生成'CUST_1003_003'

三、修改已存在的IDENTITY列值（不破坏自增） #

SQL Server默认不允许直接更新IDENTITY列，可通过IDENTITY_INSERT开关，采用“插入新行+删除旧行”的方式修正，后续自增逻辑不受影响：

-- 开启手动插入权限
SET IDENTITY_INSERT your_table ON;

-- 插入修正后的行（指定正确的entry_num）
INSERT INTO your_table (entry_num, component) VALUES (2001, 1);

-- 删除原有错误行
DELETE FROM your_table WHERE entry_num = 1001;

-- 关闭权限
SET IDENTITY_INSERT your_table OFF;

操作完成后，后续新增数据的自增会基于当前表中最大的entry_num（示例中为2001）继续递增。

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