CODEWITHSUNDEEP
sql-serveridentity-column
zey
zey

Reputation: 6103

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 ?

Upvotes: 3

Views: 30219

Answers (2)

Ravi Singh
Ravi Singh

Reputation: 2080

As suggested by Rafeel you do not need to explicitly insert values in Identity column. However, if you have deleted a row and want to a row with same ID value. Then you can do following :

SET IDENTITY_INSERT MyTable ON

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

SET IDENTITY_INSERT MyTable OFF

Upvotes: 8

Rafael Azevedo
Rafael Azevedo

Reputation: 330

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)

Upvotes: 13

Related Questions