Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here is my script of my db ,

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](25) NULL,
[Name] [nvarchar](25) NULL,
[dob] [date] NULL ,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
  (
[ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]

When I insert , I got this error

Cannot insert explicit value for identity column in table 'MyTable' when   
IDENTITY_INSERT is set to OFF.

How can I solve it ?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

When a column is defined as IDENTITY you should not include it in the insert statement SQL Server by it self will insert the value.

    INSERT INTO MYTABLE([CODE],[NAME],[DOB]) VALUES (123,'BOND','01-04-1971')

If you look at the insert statement ID column is missing but still will get a incremented value upon executing this statement.

share|improve this answer

See tictac's response. Or you can manually insert any unique identity value you want, by first executing the command:

ALTER TABLE MyTable SET IDENTITY_INSERT ON

Then run your script, followed by

ALTER TABLE MyTable SET IDENTITY_INSERT OFF
share|improve this answer
    
You'll also want some error handling here. E.g. if you try to insert a duplicate value for ID you'll get a primary key violation, which may prevent the SET OFF command from running... it's session-based, but still not tidy. –  Aaron Bertrand Jun 13 '13 at 18:09
    
True that. But then, most any operation usually requires some manual error / transaction handling, which depending on circumstances I believe should usually be rather obvious to the one creating and delivering the script. :) But yes, a good point in that SQL Server actually prohibits the latter SET command, meaning any following SET ON's within the same script and session would fail. –  Kahn Jun 14 '13 at 7:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.