sqlsql-server-2008-express

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

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.

Solution

  • Try this

    SET IDENTITY_INSERT customers ON
GO
insert into customers(id, ...)
values(201,'Singh','rajnish','101 bhandup','mumbai','mp',33321,0,null,123.89,25.00)
SET IDENTITY_INSERT customers OFF