Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.
Hello guys. I am working with MSSQL Server 2018 and I have create a table as below:
CREATE TABLE [dbo].[Admin](
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[UserName] [nvarchar](100) NULL,
[PassWord] [varchar](100) NULL,
[FullName] [nvarchar](100) NULL,
[Email] [varchar](100) NULL,
[Mobile] [varchar](20) NULL,
[IsActive] [bit] NULL,
[CreateDate] [datetime] NULL,
[IsManager] [bit] NULL,
[UserId] [int] NULL
)
I set Id
column is primay key of Admin
table. And try insert data to this table by using command below:
INSERT [dbo].[Admin] ([Id], [UserName], [PassWord], [FullName], [Email], [Mobile], [IsActive], [CreateDate], [IsManager], [UserId]) VALUES (1, N'test', N'JOR4MGP7sGiNKpELVzAgrQ22==', N'Test', N'[email protected]', N'', 1, CAST(N'2018-07-11T00:00:00.000' AS DateTime), 1, 1)
INSERT [dbo].[Admin] ([Id], [UserName], [PassWord], [FullName], [Email], [Mobile], [IsActive], [CreateDate], [IsManager], [UserId]) VALUES (5, N'test1', N'72ofvLMbX29JnV+zlGOlBg22==', N'Test 1', N'[email protected]', N'', 1, CAST(N'2020-12-23T05:43:05.853' AS DateTime), 0, 14)
But I got an error Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF. You can see detail here:
Msg 544, Level 16, State 1, Line 612
Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF.
Msg 544, Level 16, State 1, Line 613
Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF.
Msg 544, Level 16, State 1, Line 614
Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF.
Msg 544, Level 16, State 1, Line 615
Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF.
Msg 544, Level 16, State 1, Line 616
Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF.
Msg 544, Level 16, State 1, Line 617
Cannot insert explicit value for identity column in table 'Admin' when IDENTITY_INSERT is set to OFF.
Msg 544, Level 16, State 1, Line 618
Completion time: 2022-10-08T15:12:32.8866641+07:00
How can I fix this issue?
Thanks in advance.
-
N0
NoorHajee WEb Oct 08 2022
This error throw because you set
Id
column will AUTO increment by 1. So please don't add this column in yourINSERT
command.Solution 1: Remove
Id
column from your Insert command.INSERT [dbo].[Admin] ([UserName], [PassWord], [FullName], [Email], [Mobile], [IsActive], [CreateDate], [IsManager], [UserId]) VALUES (N'test', N'JOR4MGP7sGiNKpELVzAgrQ22==', N'Test', N'[email protected]', N'', 1, CAST(N'2018-07-11T00:00:00.000' AS DateTime), 1, 1) INSERT [dbo].[Admin] ([UserName], [PassWord], [FullName], [Email], [Mobile], [IsActive], [CreateDate], [IsManager], [UserId]) VALUES (N'test1', N'72ofvLMbX29JnV+zlGOlBg22==', N'Test 1', N'[email protected]', N'', 1, CAST(N'2020-12-23T05:43:05.853' AS DateTime), 0, 14)
Solution 2: Set
IDENTITY_INSERT
to ONWith this solution you can keep your Insert command, no need to change.
SET IDENTITY_INSERT [dbo].[Admin] ON -- Enable here INSERT [dbo].[Admin] ([UserName], [PassWord], [FullName], [Email], [Mobile], [IsActive], [CreateDate], [IsManager], [UserId]) VALUES (N'test', N'JOR4MGP7sGiNKpELVzAgrQ22==', N'Test', N'[email protected]', N'', 1, CAST(N'2018-07-11T00:00:00.000' AS DateTime), 1, 1) INSERT [dbo].[Admin] ([UserName], [PassWord], [FullName], [Email], [Mobile], [IsActive], [CreateDate], [IsManager], [UserId]) VALUES (N'test1', N'72ofvLMbX29JnV+zlGOlBg22==', N'Test 1', N'[email protected]', N'', 1, CAST(N'2020-12-23T05:43:05.853' AS DateTime), 0, 14) SET IDENTITY_INSERT [dbo].[Admin] OFF -- Disable here
With solution 2, it really helpful when you clone from other DB but want to keep same Id.
Hope this answer can solve your issue.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.