Insert... select in mysql to copy table data

In mysql, data in the complex tabulation can be implemented simply by using insert... select. Next I will give you an example of simple table data replication.

Syntax

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 1__ktv (title, price, number, date, area, content, num) select title, price, number, date, area, content, num from 1__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 |-the next day | 70 | remarks: 15% service charge is required. | 1 | | 2 | K1 | 1680 | 20 |-the next day | 70 | Note: 15% service charge is required. | 1 | | 3 | K1 | 1680 | 20 |-the next day | 70 | remarks: 15% service charge is required. | 1 | | 4 | K1 | 1680 | 20 |-the next day | 70 | remarks: 15% service charge is required. | 1 | + ---- + ------- + -------- + ------------------- + ------ + -------------------- + ----- + 4 rows in set (0.00 sec)

Four identical data records have been successfully copied to facilitate data testing.

The preceding statement is suitable for data insertion between two tables. For multiple tables, you can first JOIN the fields to be queried, and then form a view, and then 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

F1 is the field of Table B, f2 is the field of table c, and fields from Table B and Table c are combined through JOIN queries, then insert the data to Table a through the SELECT nested query. This satisfies this scenario. If more than two tables are required, fields can be combined in the form of multiple joins.

2. syntax errors

Note that the table alias must be set at the end of the nested query section, as shown below:

The Code is as follows:	Copy code
SELECT * FROM (SELECT f1, f2 FROM B JOIN c) AS tb

That is, the final AS tb is required (the tb name can be retrieved at will), that is, an alias is specified. An alias must be specified for each derived new table. Otherwise, the following error is 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 as follows:

The Code is as follows:	Copy code
Insert into db1_name (field1, field2) values select field1, field2 FROM db2_name

Otherwise, the following error occurs: You have an error in your SQL syntax.