Insert...select replicated table data in MySQL

Grammar

INSERT into Db1_name (field1,field2) SELECT field1,field2 from Db2_name

Instance

You can run the Insert...select syntax to solve the problem:

Insert into Hotel_ktv (Title,price, number,date,area,content,num) Select Title,price,number,date,area,content,num From Hotel_ktv;

View Results

The code is as follows

Copy Code

Mysql> select * from Hotel_ktv; +----+-------+-------+--------+-------------------+------+----------------------+-----+ | ID | Title | Price | number | Date | Area | Content | num | +----+-------+-------+--------+-------------------+------+----------------------+-----+ | 1 |  K1 |     1680 | 20 | 19:00-Next 01:00 | 70 |   Remark: Additional 15% service Charge | 1 | | 2 |  K1 |     1680 | 20 | 19:00-Next 01:00 | 70 |   Remark: Additional 15% service Charge | 1 | | 3 |  K1 |     1680 | 20 | 19:00-Next 01:00 | 70 |   Remark: Additional 15% service Charge | 1 | | 4 |  K1 |     1680 | 20 | 19:00-Next 01:00 | 70 |   Remark: Additional 15% service Charge | 1 | +----+-------+-------+--------+-------------------+------+----------------------+-----+ 4 rows in Set (0.00 sec)

4 of the same data has been successfully copied, which makes it easier for us to test data.

The above statement is more suitable for data interpolation in two tables, if multiple tables are not adapted. For multiple tables, you can join the fields you want to query, then make a view and select from:

The code is as follows	Copy Code
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, F2 is the field of Table C, a join query combines fields from table B and Table C, and then inserts a select nested query into Table A, which satisfies the scenario, and if more than 2 tables are required, you can combine the fields in the form of multiple joins.

2. Grammatical error attention

It is important to note that the nested query section must finally have a table alias set, as follows:

The code is as follows	Copy Code
SELECT * FROM (select f1,f2 from B JOIN c) as TB

That the last as TB is required (TB is a name that can be arbitrarily taken), that is, specify an alias. Each derived new table must have an alias specified, 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 add values, that is, cannot be written in the following form:

The code is as follows	Copy Code
INSERT into Db1_name (field1,field2) VALUES SELECT field1,field2 from Db2_name

Otherwise, you will also be able to complain: you have an error in your SQL syntax