Menu
Possible Duplicate:
Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
I am new to SQL. I am trying to write a INSERT query in SQL server 2008 Express edition.
The query is :
insert into customers
values(201, 'Singh', 'rajnish', '101 bhandup', 'mumbai', 'mp', 33321, 0, null, 123.89, 25.00)
But I am getting following error.
An explicit value for the identity column in table 'customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I searched stackoverflow. Found some similar type of questions but unable to understand the explanation. Kindly help me to understand the error and rectify it.
EDIT :
I tried to do :
SET IDENTITY_INSERT customers ON;
insert into customers
values(201, 'Singh', 'rajnish', '101 bhandup', 'mumbai', 'mp', 33321, 0, null, 123.89, 25.00)
SET IDENTITY_INSERT customers OFF;
but again I am getting the same error.
253
By default, it's not possible to manually insert a value directly into an identity column value, but identity values can be manually entered if you turn on a session option.
Just like regular tables, a column in a table variable can also be declared as an IDENTITY column. But unlike regular tables, specifying an explicit value in an IDENTITY column when performing an INSERT statement is not allowed and will raise this error message.
Only one identity column can be created per table.
An identity column is a numeric column in a table that is automatically populated with an integer value each time a row is inserted. Identity columns are often defined as integer columns, but they can also be declared as a bigint, smallint, tinyint, or numeric or decimal as long as the scale is 0.
If your first value is the identity column then just remove it, like this:
insert into customers
values('Singh','rajnish','101 bhandup','mumbai','mp',33321,0,null,123.89,25.00)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
