Insert an explicit value for the IDENTITY column in the SQL Server database

If we insert a value in the ID column, for example:

Insert member (id, username) values (10, 'admin ')

The query analyzer will return an error message:
Reference content
Server: Message 544, level 16, status 1, Row 1
When IDENTITY_Insert is set to OFF, explicit values cannot be inserted into the "member" column.

In ASP, an error message is returned:
Reference content
Microsoft ole db Provider for SQL Server Error '80040e14'

When IDENTITY_Insert is set to OFF, explicit values cannot be inserted into the "member" column.

Test. asp, row 13


However, in some cases, we need to manually insert the value of the ID column. For example, after deleting some records, the ID column is not consecutive, and we want to complete it. We can use a switch to turn our desires into reality:

SET IDENTITY_Insert [TableName] ON

Write in the query analyzer as follows:

SET IDENTITY_Insert member ON
Insert member (id, username) values (1, 'admin ')
SET IDENTITY_Insert member OFF

On the ASP page, you can write as follows:

Con.exe cute ("SET IDENTITY_Insert member ON" & vbcrlf & "insert member (id, username) values (2, 'abcde')" & vbcrlf & "SET IDENTITY_Insert member OFF ")

This method ensures that duplicate data is not inserted in the ID column. Otherwise, an error is returned and the insert operation is not performed. SET IDENTITY_Insert [TableName] OFF can be omitted, because after the session is completed, this switch is automatically disabled.