The operation method of SQL inserting specified data in self-incrementing column

Data tables in SQL Server tend to set self-incrementing columns, such as the ID column in the first column. When inserting new data into the data table, the self-increment column is skipped, and the column growth is carried out according to the set self-increment rule without insertion. So, what should we do if we want to insert our specified data into the self-incrementing column?

One: set up a simple test table

CREATE TABLE randy_test (id INT IDENTITY (1,1), num INT)

Second: if the data is forcibly inserted:

INSERT INTO randy_test VALUES (1BI 11)

Message 8101, level 16, status 1, line 1

Explicit values can be specified for identity columns in table 'randy_test' only if column lists are used and IDENTITY_INSERT is ON.

As you can see, the database prompt cannot specify a display value for the identity column unless the data table is set to IDENTITY_INSERT to ON

Three: set the ID column insert switch on, and then perform our insert operation

SET IDENTITY_INSERT randy_test ON INSERT INTO randy_test (id,num) VALUES (1m 11); INSERT INTO randy_test (id,num) VALUES (2m 22); set identity_insert randy_test OFF (1 line affected) (1 line affected)

The result is as follows:

SELECT * FROM randy_test AS rtid num1 11222

Summary

The above is the operation method of inserting specified data in the self-adding column of SQL introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply to you in time. Thank you very much for your support to the website!