SQL Server测试环境自增列无报错生产环境报IDENTITY_INSERT关闭错误

问题原因分析 #

这个报错的核心逻辑是：当表的列设置了identity（自增）属性时，默认不允许手动往该列插入指定值，必须先开启对应表的IDENTITY_INSERT参数才能操作。测试环境正常、生产环境报错，大概率不是全局配置差异导致，常见触发原因如下：

• 两个环境的exampleSP存储过程返回结果结构不一致
  注意INSERT ... EXEC的匹配规则是按结果集的字段顺序匹配，而非字段名匹配，哪怕你在INSERT语句后指定了列名，也只会按存储过程返回的字段顺序依次赋值，和字段名完全无关。如果测试环境的exampleSP返回的字段顺序、字段数量和生产不同，很可能刚好绕过了往自增列写值的限制，不会触发报错。
• 会话级配置差异
  IDENTITY_INSERT是会话级参数，默认值为OFF。如果你在测试环境的同一会话中之前执行过开启IDENTITY_INSERT的操作且未关闭会话，后续操作会复用该配置，不会报错；而生产环境是全新会话，默认关闭就会触发异常。
• 实际操作的表结构不一致
  确认测试环境没有重名的残留临时表、永久表，避免实际操作的表结构和预期不一致，比如测试环境实际操作的表id列没有identity属性，自然不会触发报错。

修复方案 #

方案1：确实需要写入存储过程返回的id值到自增列

显式开启临时表的IDENTITY_INSERT参数，操作完成后记得关闭：

CREATE TABLE #temptable
(
     id int identity,
     name varchar(1000)
)

SET IDENTITY_INSERT #temptable ON

INSERT INTO #temptable (id, name)
    EXEC exampleSP @value1,@value2

SET IDENTITY_INSERT #temptable OFF

方案2：不需要存储过程返回的id，希望临时表自生生成自增id

插入时不指定id列即可：

CREATE TABLE #temptable
(
     id int identity,
     name varchar(1000)
)

INSERT INTO #temptable (name)
    EXEC exampleSP @value1,@value2

方案3：业务需要以存储过程返回的id作为主键

直接把临时表的id列改为普通int类型即可，不需要设置identity属性，也不用额外操作IDENTITY_INSERT参数：

CREATE TABLE #temptable
(
     id int primary key,
     name varchar(1000)
)

INSERT INTO #temptable (id, name)
    EXEC exampleSP @value1,@value2

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