SQL insert into statement writing method explanation

Method 1: INSERT INTO t1(field1,field2) VALUE(v001,v002);

Explicitly insert only one Value

Method 2: INSERT INTO t1(field1,field2) VALUES(v101,v102),(v201,v202),(v301,v302),(v401,v402);

Method 2 is better than method 1 when inserting batch data.

[Special Note] When id is auto-increment, that is, id INT PRIMARY KEY AUTO_INCREMENT, when executing the insert into statement, all fields except id need to be listed (does it feel inconvenient? I hope MySQL will provide a simple method to mark this situation, because it is commonly used when testing data in the early days, and it is really troublesome to list all fields except id).

Method 3.1, INSERT INTO t2(field1,field2) SELECT colm1,colm2 FROM t1 WHERE ...

Here is a brief introduction. Since you can specify the columns to be inserted into talbe2 and obtain the data source through relatively complex query statements, it may be more flexible to use. However, we must also pay attention to the fact that when we specify the columns of the target table, we must fill in all non-empty columns, otherwise data insertion will not be possible. Another point that is prone to error is when we write it in the following abbreviated format:

Method 3.2: INSERT INTO t2 SELECT colm1,colm2,…… FROM t1

At this point, if we omit the columns of the target table, data will be inserted into all the columns of the target table by default, and the order of the columns after SELECT must be exactly the same as the definition order of the columns in the target table to complete the correct data insertion. This is a point that is easily overlooked and deserves attention.

[Special Note] Since the insert operation only roughly checks the [data type] of all fields in tables t1 and t2 in order, the [field name] is not checked. This is a double-edged sword, providing convenience but also carrying the potential for risk due to carelessness. During use, the order needs to be confirmed. It is recommended to use [Method 3.1] or [Method 4].

Method 4: INSERT INTO table name SET column name 1 = column value 1, column name 2 = column value 2,...;

However, using INSERT INTO SET, you cannot add data in batches. (Reference: Advantages of inserting data INSERT INTO SET in MySQL database)

This is the end of this article about how to write SQL insert into statements. For more relevant SQL insert into statement content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!