使用set identity_insert on条件插入值时的报错解决方法

报错根因 #

两个报错分别对应SQL Server IDENTITY_INSERT 特性的两个强制规则：

• Incorrect syntax near 'on'：SET IDENTITY_INSERT 是编译阶段生效的会话级设置，在低兼容级别、旧版本SQL Server中，如果将该语句放在IF包裹的BEGIN...END运行时分支内，编译阶段解析器无法识别到该设置，直接抛出语法错误。
• An explicit value for the identity column in table 'test' can only be specified when a column list is used and IDENTITY_INSERT is ON：只要给标识列插入显式值，INSERT语句必须显式写全目标列名列表。省略列列表直接写VALUES的写法，无论是否开启IDENTITY_INSERT，都会被判定为非法操作。

修正后代码（通用兼容写法） #

create table test
(
    id int identity(101, 1) not null,
    number int not null
);

-- 会话级设置放到IF块外，保证编译阶段可正常识别
set identity_insert test on;

if (1=1)
begin
    -- 插入时显式指定列名，和VALUES中的值按顺序一一对应
    insert into test (id, number)
    values (50, 328), (55, 627), (58, 419)
end;

-- 操作完成后及时关闭设置，避免影响同会话下其他表的操作
set identity_insert test off;

select * from test;

特殊场景写法 #

如果业务逻辑要求仅当IF条件成立时才开启IDENTITY_INSERT，可以将分支内的逻辑写成动态SQL。动态SQL属于独立批处理，不会触发编译阶段的识别问题：

create table test
(
    id int identity(101, 1) not null,
    number int not null
);

if (1=1)
begin
    exec sp_executesql N'
    set identity_insert test on;
    insert into test (id, number)
    values (50, 328), (55, 627), (58, 419);
    set identity_insert test off;
    '
end;

select * from test;

注意事项 #

• 同一个数据库会话中，同一时间只能为一个表开启IDENTITY_INSERT，需要操作其他带标识列的表前，必须先关闭当前表的该设置。
• 插入显式标识值时，列名顺序必须和VALUES中传入值的顺序完全对应，避免值错位。

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