This topic describes how to use SQL statements to migrate data between tables.
Migrate data between tables
Statement 1
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. |
Statement 2
Applicability
Currently, the MERGE statement is supported only in the Oracle mode of OceanBase Database.
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];
The parameters are described as follows:
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 (*).
NoticeThe columns selected must be of the same number and the same data types as those in the destination table. |
| 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 into 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