This topic describes how to use common SQL migration scripts to migrate data.
Migrate data between tables
You can use the INSERT INTO......SELECT...... statement to migrate data.
Syntax:
INSERT INTO table2
SELECT [(col_name[, col_name] ...)] FROM table1
WHERE [expr];
| Parameter | Required | Description | Example |
|---|---|---|---|
| INSERT INTO table2 | Yes | The destination table for data migration. | insert into table_B |
| SELECT [(col_name[, col_name] ...)] | Yes | The columns to be migrated. To specify all data, use an asterisk (*). Notice The number of columns specified must be the same as the number of columns in the destination table. |
select id,name,height |
| FROM table1 | Yes | The source table of data migration. | from table_A |
| WHERE [expr] | No | The filtering condition for data migration. If you do not specify this parameter, all row records specified in SELECT are migrated. | where height > 95 |
Example:
The following statement inserts eligible data in table_A to table_B:
obclient> select * from table_A;
+----+------+--------+-------+
| id | name | height | glass |
+----+------+--------+-------+
| 1 | ali | 175 | no |
| 2 | lin | 162 | no |
| 3 | hei | 172 | no |
+----+------+--------+-------+
3 rows in set (0.01 sec)
obclient> select * from table_B;
Empty set (0.00 sec)
obclient> insert into table_B select id,name,height from table_A where height > 170;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
obclient> select *from table_B;
+----+------+--------+
| id | name | height |
+----+------+--------+
| 1 | ali | 175 |
| 3 | hei | 172 |
+----+------+--------+
2 rows in set (0.00 sec)
Migrate resource units
Use the ALTER SYSTEM statement to migrate resource units.
The following statement migrates a resource unit of the specified unit ID to the destination:
ALTER SYSTEM MIGRATE UNIT = [unit_id] DESTINATION = [ip_port]The following statement cancels a resource unit migration task:
obclient> ALTER SYSTEM CANCEL MIGRATE UNIT unit_id;