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

Dung Do Tien Oct 08 2022 327

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.

Have 1 answer(s) found.
  • N

    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 your INSERT 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 ON

    With 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.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close