Search code examples
sql-serveridentity-column

IDENTITY_INSERT is set to OFF?

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 ?

Solution

  • You are trying to assign an explicit value for the identity column instead of leaving database create one to you when you are inserting data.

    You are doing something like:

    insert into MyTable (Id, Code, Name, dob) VALUES (1, 'XXX', 'xxx', 'xxx', somedate)

    instead of 

    insert into MyTable(Code, Name, dob) VALUES ('xxx', 'xxx', somedate)