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

Question

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.

Either don't insert an explicit value for the identity column or use `SET IDENTITY_INSERT customers ON` to allow you to do so. — Martin Smith, Oct 03 '12 at 07:19
I saw that answer and tried one of the solution mentioned in my edit. But it gave the same error again. — user1716251, Oct 03 '12 at 07:25
And you're again not *reading* the error "... when a column list is used **and** IDENTITY_INSERT is ON". You've addressed the second part, but not the first. — Damien_The_Unbeliever, Oct 03 '12 at 07:26

score 20 · Accepted Answer · edited Sep 14 '17 at 20:34

20

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

edited Sep 14 '17 at 20:34

Community

1
1

answered Oct 03 '12 at 07:22

Aleksandr Fedorenko

16,594
6
37
44

I tried this query too but again getting same error. – user1716251 Oct 03 '12 at 07:27
13

You need the column list. `insert into customers(id, foo, bar) values (...)` – Martin Smith Oct 03 '12 at 07:29
Ok, let me try this. Thank you. – user1716251 Oct 03 '12 at 07:32
with this work with bulk insert? – Charles Okwuagwu Apr 18 '16 at 12:59
IT does not seem to work: `bulk insert accounts(id,....) from 'c:\sql\src.txt'` – Charles Okwuagwu Apr 18 '16 at 13:00

score 4 · Answer 2 · answered Oct 03 '12 at 07:20

4

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)

answered Oct 03 '12 at 07:20

Niklas

13,005
23
79
119

RThomas · Answer 3 · 2012-10-03T07:26:02.353

When a table is using an identity field it chooses it's own values. It tracks these values to make sure that it never tries to enter a duplicate and will produce this error if you try to specify your own.

So, in your case either A insert the row without the field that has been marked as an identity field; letting the sql engine pick it's own value for that field. ( that's the whole reason your using an identity field right )

Or, B. Enable identity_insert on before running your insert ( if you have a good reason for inserting a specific value as the key ).

You also need to use a valid insert statement. Specify the columns you're inserting as well as the values.

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

3 Answers3