[Fix] - Error Cannot insert explicit value for identity column in table

Wikitechy | 1645 Views | sql server | 09 Jun 2016

 

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 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

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.




Workshop

IOT Hackathon
Webinar

Join our Community

Advertise