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

Question

I have the below error when I execute the following script. What is the error about, and how it can be resolved?

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)

Error:

Server: Msg 544, Level 16, State 1, Line 1

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

@HimanshuAhuja This question (1334012) is 10 years old, the one you linked only 1. If anything, then the newer is a duplicate. But on closer look you will that they differ (the newer one specifies IDENTITY_INSERT as ON). Please delete your flag/comment. — jasie, Oct 01 '19 at 09:06
@jasie will do that as I dont remember when i had posted this flag — Himanshu Ahuja, Oct 01 '19 at 12:43

score 548 · Answer 1 · edited Oct 03 '12 at 07:33

548

You're inserting values for OperationId that is an identity column.

You can turn on identity insert on the table like this so that you can specify your own identity values.

SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
            (OperationID,
             OpDescription,
             FilterID)
VALUES      (20,
             'Hierachy Update',
             1)

SET IDENTITY_INSERT Table1 OFF

edited Oct 03 '12 at 07:33

Martin Smith

402,107
79
682
775

answered Aug 26 '09 at 11:23

pjp

15,286
3
31
55

16

+1 exactly - turn the option to allow explicit inserts **ON**, then insert, then turn the option **OFF** again – marc_s Aug 26 '09 at 11:37
13

If you really want to add your value to the identity column, this is your answer, but on the other hand, someone have set the column to be incrementing by itself up on insert. In that case the table will keep track of the next free number, and you do not need to generate the OperationID by yourself. The new id can be fetched by SELECT SCOPE_IDENTITY(). – Hakan Winther Aug 26 '09 at 13:29
8

good answer, but make sure you know what you are doing. For me it's useful to seed a database with data where the foreign keys need to match up over different tables – Berty Apr 01 '14 at 09:50
@marc_s Why to turn it OFF again? Can't it just be set ON so that we don't need to set it ON/OFF every time we need to insert a record on the table? – Ulysses Alves Jan 25 '17 at 12:43
2

@UlyssesAlves: this option can **only** be turned on for a single table database-wide - so if you leave it on for one table, you won't be able to ever use it for another table. Also: it's not an option that should be left on - by default, you should let SQL Server handle identity values - this is **only** intended as a last resort measure for very specific cases - not a general-purpose option to leave on or off at your leisure... – marc_s Jan 25 '17 at 16:27
Explanation: An identity is a column with system generated unique ascending integer that is used as a unique identity for the record. The usual reason is to create a key/number like an invoice number etc that you want to be unique. Writing identity values yourself is fraught, it risks creating creating duplicate values and busting the table integrity. You want to be clear on what you're up to, not just ramming data into a table because you can. This might be used for exceptional circumstances like replacing lost data. – jim birch Apr 08 '20 at 03:21

score 74 · Answer 2 · answered Aug 26 '09 at 11:24

74

don't put value to OperationID because it will be automatically generated. try this:

Insert table(OpDescription,FilterID) values ('Hierachy Update',1)

answered Aug 26 '09 at 11:24

Wael Dalloul

20,176
11
45
57

6

this is the correct answer if you want automatically generated OperationID – Shaiju T May 16 '15 at 10:20

score 64 · Answer 3 · edited Aug 25 '20 at 17:46

64

Simply If you getting this error on SQL server then run this query-

SET IDENTITY_INSERT tableName ON

This is working only for a single table of database e.g If the table name is student then query look like this:

SET IDENTITY_INSERT student ON

If you getting this error on your web application or you using entity framework then first run this query on SQL server and Update your entity model (.edmx file) and build your project and this error will be resolved

edited Aug 25 '20 at 17:46

Oli Folkerd

5,658
1
19
35

answered Feb 21 '17 at 10:26

Umang Patwa

2,070
3
24
39

It was the case in my MVC web app with EF and I just deleted the model from .edmx and re-added (Right Click Update Model From Database) and clean and rebuild the project which then worked for me. Thank you @Umang Patwa – Ishwor Khanal Jun 15 '18 at 02:30

score 44 · Answer 4 · edited Sep 09 '14 at 21:04

44

Be very wary of setting IDENTITY_INSERT to ON. This is a poor practice unless the database is in maintenance mode and set to single user. This affects not only your insert, but those of anyone else trying to access the table.

Why are you trying to put a value into an identity field?

edited Sep 09 '14 at 21:04

Peter Beacom

41
1
9

answered Aug 26 '09 at 15:02

HLGEM

88,902
11
105
171

5

Affects in what way? This is a session level option not a property of the table. – Martin Smith Oct 03 '12 at 07:24
5

One time data sync between two databases that you want to maintain relationships. For example I used it to pull my product schema from one database to another – NSjonas Jan 07 '13 at 15:41
1

@NSjonas, that would be a good use of set Identity _insert table1 ON. I have seen people who wanted to use this to do something from the application that should be done with a simple insert and allowing the identity to get generated. – HLGEM Jan 07 '13 at 16:25
2

The question is "Could you specify what it is and how it can be resolved?" Your answer is a comment which raises a warning and another question instead of a proper answer that addresses the problem. – Quality Catalyst Sep 09 '16 at 23:09
yeah don't insert value in primary key. – doflamingo May 01 '17 at 06:34

score 25 · Answer 5 · edited Oct 05 '17 at 11:46

25

In your entity for that table, add the DatabaseGenerated attribute above the column for which identity insert is set:

Example:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int TaskId { get; set; }

edited Oct 05 '17 at 11:46

glennsl

23,127
11
49
65

answered Mar 17 '16 at 19:45

user902490

315
4
7

Well, true, but I really wanted an answer for EF :-) Still, it's not appropriate to the OP who may never have used, or perhaps even encountered, EF. – Auspex Oct 04 '18 at 16:22
In any case, the answer is wrong. My entity is already specified with `DatabaseGeneratedOption.Identity`, and I still get the error. It's my own fault, I'm putting pseudo-IDs in the new rows to distinguish them from each other on my web page, and I hope simply nulling them before the `insert` is sufficient. – Auspex Oct 04 '18 at 16:28

QA Specialist · Answer 6 · 2018-03-14T20:04:27.197

There are basically 2 different ways to INSERT records without having an error:

1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT

2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY "ID" MUST BE PRESENT

As per the following example from the same Table created with an IDENTITY PRIMARY KEY:

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT from the "INSERT INTO" Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error "Cannot insert explicit value for identify column in table..."

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY "ID" MUST BE PRESENT from the "INSERT INTO" Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error "Explicit value must be specified for identity column table..."

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

Plus one for actually Explaining how the flag works. Saved my day Superman — John, Jan 27 '19 at 08:51

score 13 · Answer 7 · 2017-10-05T10:45:16.700

13

you can simply use This statement for example if your table name is School. Before insertion make sure identity_insert is set to ON and after insert query turn identity_insert OFF

SET IDENTITY_INSERT School ON
/*
  insert query
  enter code here
*/
SET IDENTITY_INSERT School OFF

edited Oct 05 '17 at 10:45

answered Oct 05 '17 at 10:21

1

Could you expand on your answer a little bit to include an explanation of the command, why it works and what effect it has? – gareththegeek Oct 05 '17 at 10:24
@gareththegeek yes its for the identity column you have to make sure to make it on to insert data . – Oct 05 '17 at 10:47

score 8 · Answer 8 · answered May 19 '20 at 04:22

Note that if you are closing each line with ;, the SET IDENTITY_INSERT mytable ON command will not hold for the following lines.

i.e.
a query like

SET IDENTITY_INSERT mytable ON;
INSERT INTO mytable (VoucherID, name) VALUES (1, 'Cole');

Gives the error
Cannot insert explicit value for identity column in table 'mytable' when IDENTITY_INSERT is set to OFF.

But a query like this will work:

SET IDENTITY_INSERT mytable ON
INSERT INTO mytable (VoucherID, name) VALUES (1, 'Cole')
SET IDENTITY_INSERT mytable OFF;

It seems like the SET IDENTITY_INSERT command only holds for a transaction, and the ; will signify the end of a transaction.

score 6 · Answer 9 · answered Mar 07 '13 at 19:55

If you are using liquibase to update your SQL Server, you are likely trying to insert a record key into an autoIncrement field. By removing the column from the insert, your script should run.

<changeSet id="CREATE_GROUP_TABLE" >
    <createTable tableName="GROUP_D">
        <column name="GROUP_ID" type="INTEGER" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="INSERT_UNKNOWN_GROUP" >
    <insert tableName="GROUP_D">    

        <column name="GROUP_ID" valueNumeric="-1"/>
 ...
    </insert>
</changeSet>

score 4 · Answer 10 · answered Mar 03 '16 at 11:01

There is pre-mentioned OperationId in your query which should not be there as it is auto increamented

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)

so your query will be

Insert table(OpDescription,FilterID)
values ('Hierachy Update',1)

score 3 · Answer 11 · edited Oct 23 '19 at 00:00

3

Another situation is to check that the Primary Key is the same name as with your classes where the only difference is that your primary key has an 'ID' appended to it or to specify [Key] on primary keys that are not related to how the class is named.

edited Oct 23 '19 at 00:00

ΩmegaMan

22,885
8
76
94

answered Feb 24 '15 at 23:07

Salem Kosemani

81
7

score 3 · Answer 12 · edited Mar 03 '21 at 23:41

everyone comment about SQL, but what happened in EntityFramework? I spent reading the whole post and no one solved EF. So after a few days a found solution: EF Core in the context to create the model there is an instruction like this: modelBuilder.Entity<Cliente>(entity => { entity.Property(e => e.Id).ValueGeneratedNever();

this produces the error too, solution: you have to change by ValueGeneratedOnAdd() and its works!

score 2 · Answer 13 · answered Apr 14 '19 at 01:51

This occurs when you have a (Primary key) column that is not set to Is Identity to true in SQL and you don't pass explicit value thereof during insert. It will take the first row, then you wont be able to insert the second row, the error will pop up. This can be corrected by adding this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)] in your PrimaryKey column and make sure its set to a data type int. If the column is the primary key and is set to IsIDentity to true in SQL there is no need for this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
this also occurs when u have a column that is not the primary key, in SQL that is set to Is Identity to true, and in your EF you did not add this line of code [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Thanks this was the fix iv spent hours looking for. – Vishav Premlall May 04 '19 at 20:01 — Vishav Premlall, May 04 '19 at 20:01

score 2 · Answer 14 · answered Aug 07 '19 at 11:01

The best solution is to use annotation GeneratedValue(strategy = ...), i.e.

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column ...
private int OperationID;

it says, that this column is generated by database using IDENTITY strategy and you don't need to take care of - database will do it.

score 1 · Answer 15 · answered Feb 10 '20 at 11:16

1

If you're having this issue while using an sql-server with the sequelize-typescript npm make sure to add @AutoIncrement to ID column:

  @PrimaryKey
  @AutoIncrement
  @Column
  id!: number;

answered Feb 10 '20 at 11:16

Kate Kasinskaya

599
7
9

score 1 · Answer 16 · answered Oct 20 '20 at 21:59

1

In my case I was having set another property as key in context for my modelBuilder.

modelBuilder.Entity<MyTable>().HasKey(t => t.OtherProp);

I had to set the proper id

 modelBuilder.Entity<MyTable>().HasKey(t => t.Id);

answered Oct 20 '20 at 21:59

user3542654

103
1
6

score 0 · Answer 17 · answered Jul 16 '13 at 15:53

0

And if you are using Oracle SQL Developer to connect, remember to add /sqldev:stmt/

/sqldev:stmt/ set identity_insert TABLE on;

answered Jul 16 '13 at 15:53

Hao Deng

625
8
5

Clearly this is not the case, the question has a "sql-server" tag – DanielV Mar 17 '16 at 15:21

score 0 · Answer 18 · edited Sep 23 '16 at 00:24

0

I'm not sure what the use for the "Insert Table" is, but if you're just trying to insert some values try:

Insert Into [tablename] (OpDescription,FilterID)
values ('Hierachy Update',1);

I had the same error message come up, but I think this should work. The ID should auto-increment automatically as long as it's a primary key.

edited Sep 23 '16 at 00:24

Theresa

3,189
9
41
45

answered Sep 22 '16 at 21:33

Matthew

99
1
2
9

score 0 · Answer 19 · answered May 08 '20 at 09:48

0

I solved this problem by creating a new object every time I want to add anything to the database.

answered May 08 '20 at 09:48

Naresh Bisht

164
7

score 0 · Answer 20 · answered Aug 13 '20 at 08:38

0

In my CASE I was inserting more character than defined in table.

In My Table column was defined with nvarchar(3) and I was passing more than 3 characters and same ERROR message was coming .

Its not answer but may be in some case problem is similar

answered Aug 13 '20 at 08:38

Wasim

458
3
19

score 0 · Answer 21 · answered May 24 '21 at 12:08

im using asp.net core 5.0 and i get that error. i get that error because i was adding another data and triggering the other .SaveChanges() method like below :

 _unitOfWorkVval.RepositoryVariantValue.Create(variantValue);
 int request = HttpContext.Response.StatusCode;
 if (request == 200)
 {
     int tryCatch = _unitOfWorkCVar.Complete();
     if (tryCatch != 0)
     {
         productVariant.CategoryVariantID = variantValue.CategoryVariantID;
         productVariant.ProductID = variantValue.ProductID;
         productVariant.CreatedDate = DateTime.Now;
         _unitOfWorkProductVariant.RepositoryProductVariant.Create(productVariant);
         _unitOfWorkVval.RepositoryVariantValue.Create(variantValue);
         int request2 = HttpContext.Response.StatusCode;
         if(request==200)
         {
             int tryCatch2=_unitOfWorkProductVariant.Complete();//The point where i get that error
         }///.......

score -1 · Answer 22 · edited Mar 23 '17 at 13:06

-1

The problem raised from using non-typed DBContext or DBSet if you using Interface and implement method of savechanges in a generic way

If this is your case I propose to strongly typed DBContex for example

MyDBContext.MyEntity.Add(mynewObject)

then .Savechanges will work

edited Mar 23 '17 at 13:06

Al Foиce ѫ

3,844
10
34
45

answered Mar 23 '17 at 12:49

Mostafa Basha

45
2

score -1 · Answer 23 · answered Apr 20 '19 at 20:41

Simply delete the tables that are dragged into your .dbml file and re-drag them again. Then Clean solution>Rebuild solution> Build solution.

Thats what worked for me.

I didnt made the table on my own, I was using VS and SSMS, I followed this link for ASP.NET Identity:https://docs.microsoft.com/en-us/aspnet/identity/overview/getting-started/adding-aspnet-identity-to-an-empty-or-existing-web-forms-project

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

23 Answers23

Linked

Related