SQL Server 2014迁移表后插入IDENTITY列报错求助

我帮你整理几个针对这个问题的可行解决办法，毕竟跨库迁移时IDENTITY列的坑确实挺常见的，咱们一步步来排查：

1. 先确认IDENTITY列的配置是否真的生效 #

首先得确保你新增的IDENTITY列确实被正确设置了自动增量属性，别忙活半天属性没加上。执行下面的SQL语句检查：

SELECT name, is_identity, seed_value, increment_value
FROM sys.columns
WHERE object_id = OBJECT_ID('你的表名') AND is_identity = 1;

• 如果结果为空或者is_identity显示为0，说明IDENTITY属性没生效。这时候如果表还没数据，可以直接修改列：

ALTER TABLE 你的表名
ALTER COLUMN 你的IDENTITY列名 INT IDENTITY(1,1);

• 如果表已经有数据，直接修改会报错，建议新增一个临时IDENTITY列，把旧数据的主键值导过去，然后删除旧列，再把临时列重命名为原来的主键列名。

2. 正确使用IDENTITY_INSERT开关（重点！） #

你遇到的报错大概率是IDENTITY_INSERT的使用流程不对，这个开关的使用有严格要求：

• 第一步：开启指定表的IDENTITY_INSERT（注意：一次只能给一个表开这个开关）

SET IDENTITY_INSERT 你的表名 ON;

• 第二步：执行INSERT语句时必须显式写出所有列名（包括IDENTITY列），绝对不能用省略列名的INSERT INTO 表名 VALUES(...)写法，比如：

INSERT INTO 你的表名 (ID列名, 用户名, 邮箱, 创建时间)
VALUES (1001, '张三', 'zhangsan@example.com', '2024-05-20');

• 第三步：插入完成后立刻关闭开关，别留着影响后续操作

SET IDENTITY_INSERT 你的表名 OFF;

为啥要显式列名？因为开启IDENTITY_INSERT后，SQL Server要求你明确指定要插入的IDENTITY值，不能让它自动生成，所以必须把所有要插入的列都列出来，避免歧义

3. 跳过Access中转，直接从旧SQL Server迁移数据（最稳妥） #

其实你绕了Access一圈反而容易丢属性，不如直接用SQL Server自带的工具迁移：

• 先在新服务器上复刻旧服务器的表结构：在旧SSMS里右键目标表 → 脚本表为 → CREATE到 → 新查询编辑器，把生成的脚本拿到新服务器执行，这样主键、IDENTITY属性都会完美保留。
• 然后用导入导出向导直接迁移数据：打开新服务器的SSMS，右键目标数据库 → 任务 → 导入数据，数据源选SQL Server Native Client，指向旧服务器，选择要迁移的表，向导会自动处理IDENTITY列的插入，不用手动折腾。

4. 排查是否有其他会话占用了IDENTITY_INSERT #

有时候报错是因为别的查询窗口开了同一个表的IDENTITY_INSERT没关，导致你这边操作冲突。可以用下面的SQL查当前开启IDENTITY_INSERT的表：

SELECT 
    OBJECT_NAME(object_id) AS 表名, 
    is_identity_insert_on
FROM sys.tables
WHERE is_identity_insert_on = 1;

如果查到你的表在列表里，找到对应的会话（可以用sp_who2查会话ID），要么手动关闭那个窗口，要么用KILL 会话ID;终止会话（注意：终止前确认那个会话没有重要操作）。

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