IDENTITY_INSERT is set to OFF?

sql-server identity-column
zey picture zey · Jun 13, 2013 · Viewed 26.6k times · Source

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 ?

Answer

Rafael Azevedo picture Rafael Azevedo · Jun 13, 2013

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)

Related questions

Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine …

Read More
How do you determine what SQL Tables have an identity column programmatically

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL. Results would be something like: TableName, ColumnName

Read More
BULK INSERT with identity (auto-increment) column

I am trying to add bulk data in database from CSV file. Employee table has a column ID (PK) auto-incremented. CREATE TABLE [dbo].[Employee]( [id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Address] [varchar](50) NULL ) ON [PRIMARY] I am using …

Read More