0

I get an error

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

I have set the IDENTITY_INSERT to ON by using the query SET IDENTITY_INSERT [Database1].[dbo].[Table1] ON, but still i get this error. In my case I am trying to copy the data of one table to an another so copying the ID. I have set the identity_insert to ON. I do not know why i get this error.

CREATE TABLE [Database1].[dbo].[Table1]
   (ID int IDENTITY,
    Machinetyp nvarchar(255),
    Flaeche float not null,
    Losnumberr nvarchar(255),
    Current float not null,
    RK float not null,
);
GO
SET IDENTITY_INSERT [Database1].[dbo].[Table1] ON
Ilyes
  • 14,640
  • 4
  • 29
  • 55
user9630935
  • 349
  • 1
  • 4
  • 18
  • As you have shown no code all we can do is guess. My guess is that you execute the statement in a `SET IDENTITY_INSERT ` in a different scope that has exited – Martin Smith Jun 14 '19 at 08:35
  • Are you the owner of the table or have ALTER permission on the table? Also, please edit your question to provide your code. – Ilyes Jun 14 '19 at 08:35
  • @Sami Yes i am the owner of the table. Yes i will put my code. – user9630935 Jun 14 '19 at 08:40
  • @user9630935 You need to provide the `INSERT` statement too. – Ilyes Jun 14 '19 at 08:43
  • @sami The insert statement i call it in Groovy. – user9630935 Jun 14 '19 at 08:45
  • 1
    Are you trying to permanently leave `IDENTITY_INSERT` turned on on `Table1`? if so, why even use the `IDENTITY` property? – Thom A Jun 14 '19 at 08:45
  • @user9630935 So you need to `IDENTITY_INSERT ON` in Groovy too, and re-set it to `OFF` when you done. – Ilyes Jun 14 '19 at 08:46
  • @user9630935 `SET IDENTITY_INSERT` is session bound. You can't turn it on in the `CREATE` statement and then `INSERT` from the application and expect it to use the setting from another session. – Thom A Jun 14 '19 at 08:46
  • 2
    But, still sounds like you shouldn't be using `IDENTITY` at all. – Thom A Jun 14 '19 at 08:47
  • @Larnu I have a column ID in an other table. I wanted to copy into this table1. What do you suggest me doing. – user9630935 Jun 14 '19 at 08:48
  • So `table1` is a temporary object? Will it have more rows inserted into it after you "copy" the data? – Thom A Jun 14 '19 at 08:49
  • @Larnu Its more like synchronization of one table into another. Yes more rows will be inserted. I have a live database having a table. I wanted a backup database table – user9630935 Jun 14 '19 at 08:54
  • 1
    Then, as @Sami said, use `SET IDENTITY_INSERT ON` (and `OFF`) in Groovy. – Thom A Jun 14 '19 at 08:57
  • okie. I will try it out. Thank you both. – user9630935 Jun 14 '19 at 09:00

0 Answers0