Reprinted from:/RoadGY/archive/2011/07/22/
1. Syntax introduction
There are three tables a, b, and c. Now you need to check the values of several fields from table b and table c respectively to insert them into the corresponding fields in table a. For this case, the following statement can be used to implement it:
INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name
If the structure of the two tables is the same, it can also be like this:
INSERT INTO [BI_Single] SELECT TOP 999 * FROM BI_Single_copy
The above statement is more suitable for interpolation of data from two tables, and if multiple tables are used, it will not be suitable. For multiple tables, you can JOIN the fields that need to be queried first, then form a view and then SELECT FROM:
INSERT INTO a (field1,field2) SELECT * FROM(SELECT b.f1,c.f2 FROM b JOIN c) AS tb
where f1 is the field of table b and f2 is the field of table c. The fields from table b and table c are combined through JOIN query, and then inserted into table a through SELECT nested query, which satisfies this scenario. If more than 2 tables are needed, then fields can be combined in the form of multiple JOINs.
2. Pay attention to syntax errors
It should be noted that the nested query part must have table alias at the end, as follows:
SELECT * FROM (SELECT f1,f2 FROM b JOIN c) AS tb
That is, the last AS tb is necessary (the name tb can be taken at will), that is, specify an alias. Each derived new table must specify an alias, otherwise the following error will be reported in mysql:
ERROR 1248 (42000): Every derived TABLE must have its own alias
In addition, INSERT INTO SELECT in MySQL cannot be added with VALUES, that is, it cannot be written in the following form:
INSERT INTO db1_name(field1,field2) VALUES SELECT field1,field2 FROM db2_name
Otherwise, an error will also be reported: You have an error in your SQL syntax