[Fix] - Error Cannot insert explicit value for identity column in table
Scenario :
- When inserting explicit value for identity column, the error occurs.
Error :
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.
Reason for the error :
- Insert a record with values for all columns, including an IDENTITY Column.
Fix 1 :
1. Simply remove the value for identity column, it not required.
2. When the requirement is to insert a value follow the below code:
SET IDENTITY_INSERT wikiTechy_Table ON
INSERT INTO wikiTechy_Table(IDENTITY Column, Column1,...,ColumnN)
VALUES (IDENTITY Value,Value1, ...., ValueN)
SET IDENTITY_INSERT wikiTechy_Table OFF
Note :
- Ensure that the insert statement includes all column names, otherwise will again lead to a new error.
Error :
Msg 8101, Level 16, State 1, Line 1.
An explicit value for the identity column in table 'Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Reason for the error: Incorrect Insert Statement.
Fix 2 :
INSERT INTO WikiTechy_Table VALUES (IDENTITY Value, Value1, ...., ValueN)
- The above statement missed out to list the column names explicitly. Hence use the below command:
INSERT INTO WikiTechy_Table (IDENTITY Column, Column1...ColumnN)
VALUES (IDENTITY Value, Value1, ...., ValueN)
Applies to sqlserver version :
- SQL Server 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
Related Error Tags :
- how to rectify error "IDENTITY_INSERT is set to OFF"
- sql server - IDENTITY_INSERT is set to OFF?
- Msg 544 - Cannot insert explicit value for identity column in table <Table Name> when IDENTITY_INSERT is set to OFF.
- sql server - Set IDENTITY_INSERT ON is not working
- sql server - Error getting inserting data into table
- identity - IDENTITY_INSERT ON not working - SQL Server 2008 R2
- How to insert values into IDENTITY column in SQL Server
- Msg 8101, Level 16, State 1, Line 1
- SQL INSERT w/ IDENTITY Columns
- sql - Cannot insert explicit value for identity column in table 'table'.
- sql server - SQL Cannot insert explicit value for identity column in table.
