SQL statement to cancel the auto-increment column attribute

Because in the SQL-SERVER, the auto-incrementing column property cannot be directly modified, but you can achieve it through the following method:

1. If you only insert a specified value, use the following statement to temporarily cancel the operation.

Set identity_insert tablename on
Insert into tablename (XX, XX) values (XX, XX)
Set identity_insert tablename off

2. Add a column, delete the auto-incrementing column, and modify the column name.

Alter table a add XXX int
Update a set xxx = ID
Alter table a drop column ID
Exec sp_rename 'xxx', 'id', 'column'

3. By modifying the system's column attributes of the table, improper use of this method may cause other unexpected errors.

Sp_configure 'Allow Update', 1
Reconfigure with override
Go
Update syscolumns set colstat = 0 where colstat = 1 and ID = object_id ('tablename ')
Go
Sp_configure 'Allow Update', 0
Reconfigure with override