SQL statement-set identity insert usage

To insert the value to the automatic number (or the ID column, identity), set identity_insert example: 1. create Table products (ID int identity primary key, product varchar (40. try the following operations in the Table: insert into products (ID, product) values (3, 'garden shovel') results in an error: "When identity_insert is set to off, explicit values cannot be inserted into the identifiers column in The 'products' table." 3. Use set identity_insert products oninsert into products (ID, product) values (1, 'garden shovel') to return correct results. 4. create another table products2 and try the same insert operation: Create Table products2 (ID int identity primary key, product varchar (40) and then execute: Set identity_insert products2 oninsert into products2 (ID, product) values (1, 'garden shovel') causes an error: "Table 'material. DBO. products' identity_insert is already on. You cannot perform the set operation on the table 'products2 ." Run: Set identity_insert products offset identity_insert products2 oninsert into products2 (ID, product) values (2, 'garden shovel. 5. perform the following operations: Set identity_insert products2 oninsert into products2 select * from products: "Only when the list of columns is used and identity_insert is on, you can specify an explicit value for the ID column in 'products2 'of the table." 6. changed to: Set identity_insert products2 oninsert into products2 (ID, product) Select * from products. Conclusion: 1. at any time in each connection session, only identity_insert on can be set for one table, and the setting is only valid for the current session; 2. when performing the insert operation on the ID column, you must list the ID column (of course, you also need to list other related columns ). Appendix: Set identity_insert allows explicit values to be inserted into the table's ID column. Syntax set identity_insert [database. [owner.] {table} {on | off} parameter database is the name of the database where the specified table resides. The owner is the name of the table owner. Table is the name of the table containing the ID column. At any time, the identity_insert attribute of only one table in the session can be set to on. If a table has set this attribute to on and sends a set identity_insert on statement to the other table, Microsoft SQL server returns an error message, indicates that set identity_insert has been set to on and reports that this attribute has been set to on. If the inserted value is greater than the current table id value, SQL Server automatically uses the new value as the current ID value. Set identity_insert is set during execution or running, rather than during analysis. The permission execution permission is granted to the SysAdmin fixed server role and db_owner, db_ddladmin fixed database role, and object owner by default. In the following example, create a table with an ID column and show how to use set identity_insert to fill gaps in the ID values caused by the delete statement. -- Create products table. create Table products (ID int identity primary key, product varchar (40) Go -- inserting values into products table. insert into products (product) values ('screwdriver ') insert into products (product) values ('hammer') insert into products (product) values ('saw ') insert into products (product) values ('shovel') Go -- create a gap in the identity values. delete products where Product = 'saw 'goselect * From productsgo -- attempt to insert an explicit id value of 3; -- shocould return a warning. insert into products (ID, product) values (3, 'garden shovel ') Go -- set identity_insert to on. set identity_insert products ongo -- attempt to insert an explicit id value of 3 insert into products (ID, product) values (3, 'garden shovel '). goselect * From productsgo -- drop Products table. drop table productsgo