Cannot insert explicit value for identity column

Question

Post reply

Cannot insert explicit value for identity column

pallavi.unde

SSCommitted

Points: 1987
More actions
December 13, 2011 at 10:36 pm

#249771

when i connect the database to s/w application and trying to insert a record
i getting this error :-
Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF.
I have given all permission to the user for insert,delete and update,
also the identity specification is on.
What would be the problem here????
Thanks & Regards,
Pallavi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Please verify schema & table name in above command.

http://msdn.microsoft.com/en-us/library/ms188059.aspx

Divine Flame SSCoach Points: 15943 More actions · Answer 2

pallavi.unde (12/13/2011)
when i connect the database to s/w application and trying to insert a record
i getting this error :-
Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF.
I have given all permission to the user for insert,delete and update,
also the identity specification is on.
What would be the problem here????

The message is self explanatory. You need to set IDENTITY_INSERT ON for that particular table.

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable (ID,TextVal) VALUES (10,'Some Data')

SET IDENTITY_INSERT MyTable OFF

Sujeet Singh

baabhu SSCertifiable Points: 6205 More actions · Answer 3

If you are having identity columns, why are inserting the values through application.

Please use column names in the insert statement and exclue the identity column.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

baabhu (12/14/2011)
If you are having identity columns, why are inserting the values through application.
Please use column names in the insert statement and exclue the identity column.

Agree. It's true for most of the cases but if you really need to populate (re-populate) the tables with identity columns then you will face following error and we have already shared the solution here.