Error when trying to conditionally insert values and using `set identity_insert on`

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’.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

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.