Insert an explicit value for the identity (identity) column in the SQL Server database _mssql

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

Insert member (ID,USERNAME) VALUES (' admin ')

The error message is returned in the Query Analyzer:
Reference content
Server: Message 544, Level 16, State 1, line 1
When IDENTITY_INSERT is set to OFF, you cannot insert an explicit value into an identity column in table ' member '.

An error message is returned in the ASP program:
Reference content
Microsoft OLE DB Provider for SQL Server error ' 80040e14 '

When IDENTITY_INSERT is set to OFF, you cannot insert an explicit value into an identity column in table ' member '.

Test.asp, Line 13


But in some cases we need to manually insert the value of the identity column, such as deleting some records, the identity column is not contiguous, and we want to make it up. We use a switch to make a wish come true:

SET Identity_insert [TableName] On

In the Query Analyzer, write this:

SET IDENTITY_INSERT member on
Insert member (ID,USERNAME) VALUES (1, ' admin ')
SET Identity_insert member off

You can write this on the ASP page:

Con.execute ("SET Identity_insert member on" & vbCrLf & "Insert Member (Id,username) VALUES (2, ' ABCDE ')" & VBCRL F & "SET Identity_insert member off")

Use this method to ensure that the identity column does not insert duplicate data or that an error is returned, and the insert operation does not occur. set identity_insert [tablename] off  can actually be omitted, because when the session is finished, the switch shuts down automatically.