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.

Related Q&A May You Like

  1. How to convert timestamp to date in MySQL Select query?
  2. MySql throw no database selected, select the default DB to be used by double-clicking its name
  3. How to generate whole sql database script in MySql Workbench
  4. Error querySrv ENODATA _mongodb._tcp.456d in MongoDB Compass
  5. What is best way to convert datetime to date in Sql Server?
  6. How to compare date without time with now() in PostgreSQL?
  7. MySQL Error Code: 1175. safe update mode update a table without a WHERE
  8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements in MySQL
  9. ERROR: column must appear in the GROUP BY clause or be used in an aggregate function in PostgreSQL
  10. Calculating distance between two positions(lat, long) in SQL Server
  11. MySQL Error: 'Access denied for user 'root'@'localhost'
  12. Error: /opt/lampp/bin/mysql.server: 264: kill: No such process
  13. How to get the total of current connection in PostgreSQL?
  14. How to backup and restore MSSQL Server database in Ubuntu
  15. ERROR: OFFSET must not be negative in PostgreSQL
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.

Most Quizs Popular

Start Test

Javascript - Functions
Start Test

Asp.Net Core - Exam test 01
Start Test

Asp.Net Core - Exam test 02
Start Test

C# - Level Beginner

Popular Tips

This site uses cookies so that we can remember you and understand how you interact with our website. This allows us to improve and customize your browsing experience. See our Cookies Policy.
X Close