This topic describes how to use SQL statements to migrate data between tables.
Migrate data between tables
INSERT INTO target_table_name[(target_col_name[, target_col_name] ...)]
SELECT [(source_col_name[, source_col_name] ...)]
FROM source_table_name
[WHERE expr];
The parameters are described as follows:
Parameter |
Description |
|---|---|
| target_table_name | The destination table to which data is migrated. |
| target_col_name | The name of a column in the destination table. To update data for all columns in the destination table, you can omit the column names. |
| source_col_name | The name of a column in the source table. Select the columns to be migrated. To select all data, use an asterisk (*).
NoticeThe number of columns selected must be the same as the number of columns in the destination table. |
| source_table_name | The source table from which data is migrated. |
| WHERE expr | The filtering condition for data migration. If you do not specify this parameter, all row records specified in SELECT are migrated. |
Example
Insert data that meets the condition age > 10 in the tbl1 table into the tbl2 table.
obclient [test]> SELECT * FROM tbl1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | ab | 8 |
| 2 | bc | 18 |
| 3 | cd | 14 |
| 4 | de | 19 |
| 5 | ef | 6 |
| 6 | fg | 15 |
+------+------+------+
6 rows in set
obclient [test]> DESC tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1 | int(11) | YES | | NULL | |
| col2 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set
obclient [test]> SELECT * FROM tbl2;
Empty set
obclient [test]> INSERT INTO tbl2 SELECT id,age FROM tbl1 WHERE age > 10;
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient [test]> SELECT * FROM tbl2;
+------+------+
| col1 | col2 |
+------+------+
| 2 | 18 |
| 3 | 14 |
| 4 | 19 |
| 6 | 15 |
+------+------+
4 rows in set