SQL Server error: "Cannot insert explicit value for identity column" even when I SET IDENTITY_INSERT ON

Question

I REALLY review several times, that's the reason I am asking; looking for guidance...

I have one table, as the script below. Then, I set IDENTITY_INSERT ON. Then I try to do an insert select, (I NEED the very same ids)

I keep getting this error:

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Table1' when IDENTITY_INSERT is set to OFF.

Does anybody knows why? Any set up at DB level can overrule the IDENTITY_INSERT ON?

I appreciate any advice. Thanks in advance and kind regards.

Script to table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].Table1
(
    [TableId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [RowVersion] [timestamp] NOT NULL,
    [AddedDate] [datetime2](7) NOT NULL,
    [stuff2] [int] NOT NULL,
    [ModifiedDate] [datetime2](7) NOT NULL,
    [LastModifiedBy] [int] NOT NULL,

    CONSTRAINT [Table1_PK] 
        PRIMARY KEY CLUSTERED ([TableId] ASC)
) ON [PRIMARY]
GO

The insert statement:

SET IDENTITY_INSERT [dbo].Table1 ON;

INSERT INTO [dbo].Table1 ([TableId], [Name], [AddedDate], [stuff2], [ModifiedDate], [LastModifiedBy])
    SELECT 
        [RoleID], [Name], [AddedDate], [stuff2], [ModifiedDate], [LastModifiedBy] 
    FROM 
        [dbo].Table2

It's not about the `IDENTITY INSERT` it's about the `PRIMARY KEY` Constraint cause your trying to insert the same ID for 1 or multiples times. — Ilyes, Nov 22 '17 at 00:35
@Sami. really? I have inserted pk before.. However, I will try to drop the PK and try again. I appreciate you comment.:D — Negarrak, Nov 22 '17 at 00:39
Also **do not** cut the error msg please `Msg 544, Level 16, State 1, Line 2` is not the whole error message, please edit the question and add the whole error message you have. — Ilyes, Nov 22 '17 at 00:39
Msg 544, Level 16, State 1, Line 2 Cannot insert explicit value for identity column in table 'Table1' when IDENTITY_INSERT is set to OFF. Thank you sir, for your help. — Negarrak, Nov 22 '17 at 00:42
oh!, that is because I modified a little to don't give the real DB structure. — Negarrak, Nov 22 '17 at 00:53

score 3 · Answer 1 · answered May 19 '20 at 04:26

I had the same error even though I had run the SET IDENTITY_INSERT mytable ON command on my table. I realized it was because I was closing lines in the query script.

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.

Negarrak · Accepted Answer · 2017-11-22T00:59:48.837

2

Thank you so much @Sami, you help me to realize the right path. It turns out, you can just use, IDENTITY_INSERT to one table at time (for obvious reasons is not a thing I do often). When I did for several tables at time, I saw the error, but as the name of the tables were similar, I thought it was throwing an error because I ran the Identity_insert before on the same table, but it was because it was taken by the other table. I didn't realized until I review the Error messages one by one. :P :D

https://dba.stackexchange.com/questions/12650/why-is-identity-insert-on-only-allowed-on-one-table-at-a-time

edited Nov 22 '17 at 00:59

answered Nov 22 '17 at 00:52

Negarrak

375
1
4
11

2

That's why you should **always** `SET IDENTITY_INSERT` to `OFF` when you finish. – Ilyes Nov 22 '17 at 01:00
That's true! :D – Negarrak Nov 22 '17 at 01:03
1

select 'set identity_insert ['+s.name+'].['+o.name+'] off' from sys.objects o inner join sys.schemas s off s.schema_id=o.schema_id where o.[type]='U' and exists(select 1 from sys.columns where object_id=o.object_id and is_identity=1) – Negarrak Nov 22 '17 at 01:03
Even you fix this issue you will need to drop you PK too, or the error `Violation of PRIMARY KEY constraint 'Table1_PK'. Cannot insert duplicate key in object 'dbo.Table1'` will be throw. – Ilyes Nov 22 '17 at 01:06
just if you insert wrong data. Otherwise, if the information is consistent, it won't be required. :D – Negarrak Nov 28 '17 at 04:09

score 0 · Answer 3 · answered Jun 28 '18 at 02:27

I had the same error, then i found out that i was inserting an explicit value while my PK is set to Is Identity = true, while my relation was 1:1..0, meaning my child table uses the PK of the Parent table. [DatabaseGenerated(DatabaseGeneratedOption.Identity)] will only work if the Is Identity = true and there is no conflict in your table.

SQL Server error: "Cannot insert explicit value for identity column" even when I SET IDENTITY_INSERT ON

3 Answers3