This topic describes how to use SQL statements to migrate data between tables.
Migrate data between tables
Statement 1
Use the INSERT INTO... SELECT... statement to migrate data between tables. For more information, see INSERT (MySQL Mode) or INSERT (Oracle Mode).
INSERT INTO target_table_name[(target_col_name[, target_col_name] ...)]
SELECT [(source_col_name[, source_col_name] ...)]
FROM source_table_name
[WHERE expr];
| 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 (*).
Notice |
| 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. |
Statement 2
Use the MERGE INTO... INSERT... statement to migrate data between tables. For more information, see MERGE.
Feature applicability
The
MERGEstatement supports only OceanBase Database in Oracle mode.
MERGE INTO target_table_name
USING source_table_name
ON (expr)
WHEN NOT MATCHED THEN INSERT VALUES((source_col_name[, source_col_name] ...))
[WHERE expr];
| Parameter | Description |
|---|---|
| target_table_name | The destination table to which data is migrated. |
| source_table_name | The source table from which data is migrated. |
| ON (expr) | The condition for joining the source and target tables. |
| 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 (*).
Notice |
| WHERE expr | The filtering condition for data migration. |
Example 1
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
Example 2
If a row in the tbl1 table is not equal to any row in the tbl2 table, the row in the tbl1 table is inserted in to the tbl2 table when this row meets the tbl1.age < 10 condition. Rows that meet the tbl1.id=tbl2.col1 and tbl1.age=tbl2.col2 condition are not inserted into the tbl2 table.
obclient [SYS]> 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 [SYS]> SELECT * FROM tbl2;
Empty set
obclient [SYS]> MERGE INTO tbl2
USING tbl1
ON (tbl1.id=tbl2.col1 and tbl1.age=tbl2.col2)
WHEN NOT MATCHED THEN INSERT VALUES(tbl1.id,tbl1.age)
WHERE tbl1.age < 10;
Query OK, 2 rows affected
obclient [SYS]> SELECT * FROM tbl2;
+------+------+
| COL1 | COL2 |
+------+------+
| 1 | 8 |
| 5 | 6 |
+------+------+
2 rows in set