Identity_insert is set to off in sql

How many times you might came across this issue -

“An explicit value for the identity column in table ‘***’ can only be specified when a column list is used and IDENTITY_INSERT is ON.”

I would say its common issue which occurs when we are trying to insert the value to “IDENTITY” column in a table.

Lets look at why this issue comes up and what it means -

First what you mean by IDENTITY column -> These are commonly used as primary keys in our database tables.

How this issue comes up ?

Ok to answer this question lets create a sample table in our SQL Server -

Error thrown – "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."

Lets solve it now -

Execute this now. Worked !!!!

Simple isn’t it.

Hope its helpful too.