SQL Server error: "Cannot insert explicit value for identity column" even when I SET IDENTITY_INSERT ON
Solution 1
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
Solution 2
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.
Comments
-
Negarrak almost 2 years
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
-
Ilyes over 6 yearsIt's not about the
IDENTITY INSERT
it's about thePRIMARY KEY
Constraint cause your trying to insert the same ID for 1 or multiples times. -
Negarrak over 6 years@Sami. really? I have inserted pk before.. However, I will try to drop the PK and try again. I appreciate you comment.:D
-
Ilyes over 6 yearsAlso 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. -
Negarrak over 6 yearsMsg 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 over 6 yearsoh!, that is because I modified a little to don't give the real DB structure.
-
-
Ilyes over 6 yearsThat's why you should always
SET IDENTITY_INSERT
toOFF
when you finish. -
Negarrak over 6 yearsThat's true! :D
-
Negarrak over 6 yearsselect '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)
-
Ilyes over 6 yearsEven 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. -
Negarrak over 6 yearsjust if you insert wrong data. Otherwise, if the information is consistent, it won't be required. :D