How to use INSERT+SELECT in MySQL

The use of INSERT+SELECT in MySQL

Use scenarios

Combining fields from multiple tables and then inserting them into another new table through a SQL statement.

1. Between the two tables

Find the value of a field from one table and insert it into the corresponding field in another table

INSERT INTO tab1(field1,field2) SELECT field1,field2 FROM tab2;

2. Between the three tables

Find the value of the field from two tables and insert it into the corresponding field in another table

INSERT INTO tab1 (field1, field2) 
SELECT * 
FROM
  (SELECT 
    tab2.field1, tab3.field2 
  FROM tab2 
  JOIN tab3 
    ON  = ) AS tab ;

3. Between multiple tables

Find the value of the field from multiple tables and insert it into the corresponding field in another table

INSERT INTO tab1 (field1, field2, field3, ... ) 
SELECT * 
FROM
  (SELECT 
    tab2.field1, tab3.field2, tab4.field3,  ...
  FROM tab2 
  JOIN tab3 
    ON  = 
  JOIN tab4 
    ON  =   
    ...
    ) AS tab ;

The fields from table tab2 and table tab3 are combined through the join query, and then inserted into table tab1 through the select nested query. If more than 2 tables are needed, the fields can be combined in multiple joins.

It should be noted that the nested query part must have a table alias at the end.

This method requires that the target table must exist.

There is another existence that does not require a target table, which is SELECT INTO

Use of SELECT INTO FROM in MySQL

As another means of copying table data, the result obtained is the same as the insert into select mentioned above.

However, select into does not require the target table to exist, the target table will be automatically created during the query process.

select[column1,column2...] into [Target table name] from [Original table name]

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.