2

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 ?

5

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.

2

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
2
  • 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. 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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