SQL IDENTITY_INSERT case study

Generally speaking, once a column in a data table is set as an identity column, it is not possible to manually insert the display value of the identity column into the identity column. However, you can manually insert display values ​​into identity columns by setting the SET IDENTITY_INSERT property.

Writing method:

1. SET IDENTITY_INSERT table name ON: Indicates that the insertion mode for the identity column display value is turned on, allowing manual insertion of data for the identity column display value.
2. SET IDENTITY_INSERT table name OFF: Indicates turning off the insert operation of the identity column display value. The identity column does not allow manual insertion of display values.

Note: The IDENTITY_INSERT ON and OFF appear in pairs, so after performing a manual insert operation, remember to set IDENTITY_INSERT to OFF, otherwise the next automatic insert of data will fail.

For example:

Create a table b1, with b_id as the identification column. Set it as a unique identifier, starting from 1. Each time new data is inserted, the value increases by 1 and is not allowed to be empty:

CREATE table b1
(
b_id int identity(1,1) primary key not null,   
b_name varchar(20) null
)

When the b_id column in table b1 is set as a unique identifier column, the value of the IDENTITY_INSERT property is set to OFF by default. The identity column b_id does not allow manual insertion of display values. The system can only automatically insert display values ​​in sort order.

1. When IDENTITY_INSERT is OFF, insert two records into table b1 and manually insert display values ​​into the identity column:

Code 1:

insert into b1(b_id,b_name) values(1,'Lily')
insert into b1(b_id,b_name) values(2,'阿呆')

Insert result:

Code 2:

insert into b1(b_name) values('Lily')
insert into b1(b_name) values('阿呆')

Insert result:

Note: When IDENTITY_INSERT is set to OFF, manual insertion of display values ​​into the identity column is not allowed. The system can only automatically insert display values ​​into the identity column.

2. When IDENTITY_INSERT is ON, insert two records into table b1 and manually insert display values ​​into the identity column:

Code 1:

set identity_insert b1 on -- Enable the insert mode for the identity column. The identity column allows manual insertion of display values. insert into b1(b_id,b_name) values(8,'小白') -- Manually insert the display value of the identity column b_id to 8
insert into b1(b_id,b_name) values(9,'小黑')
set identity_insert b1 off -- Disable the insert operation on the identity column. The identity column does not allow manual insertion of display values.

Insert result:

Code 2:

set identity_insert b1 on -- Enable the insert mode for the identity column. The identity column allows manual insertion of display values. insert into b1(b_name) values('小胖') -- Manually insert the display value of the identity column b_id to 8
insert into b1(b_name) values('Xiaobao')
set identity_insert b1 off -- Disable the insert operation on the identity column. The identity column does not allow manual insertion of display values.

Insert result:

Note: When setting IDENTITY_INSERT to ON, you must specify the display value to be inserted into the identity column and manually insert the display value into the identity column. Because the system will no longer automatically insert the display value of the identity column. Therefore, you must manually insert display values ​​into the identity column.

This is the end of this article about the detailed case of SQL IDENTITY_INSERT. For more information about the role of SQL IDENTITY_INSERT, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!