Error when trying to conditionally insert values and using `set identity

Here is some testing code that simulates the issue I'm dealing with in a project:

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

if (1=1)
begin
    set identity_insert test on;

    insert into test 
    values (50, 328), (55, 627), (58, 419)

    set identity_insert test off;
end;

select * from test;

This generates an error:

Incorrect syntax near 'on'.

When the set identity_insert test on; statement is moved before the if block then this error is generated:

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.

How can this be changed so it works?

CodePudding user response：

I get the error below when using SSMS. I tried both 2008 and 2019 compatibility with the same result.

Msg 8101, Level 16, State 1, Line 11 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.

When I updated the query to include the column names in the insert as indicated by the error, it worked without error.

    insert into test (id, number)
    values (50, 328), (55, 627), (58, 419)

I am going to assume for a moment the issue is the "generic" error which is not very helpful. It's common for the original SQL Server error message to get wrapped. In a catch, I normally follow the inner exception path to it's end to get the "real" SQL Server error instead of the generic message. I'd be curious if this is the case. If so, I'd guess the nesting in code or the sql execution stack has something to do with it.

CodePudding user response：

Incorrect syntax near 'on'.

I am not sure about this one.

But the second error is generally what you are up against. The error is clear: if you want to manually insert values for an identity column, you need a column list, which your example doesn't have:

insert into test (column1,column2)
    values (50, 328), (55, 627), (58, 419)