How to Quickly Migrate Data from One Table to Another

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何快速迁移一张表的数据 到 另一张表

| username: Hacker_6ASfgBFe

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
Background: Converting a regular table to a partitioned table
A new partitioned table has been created, and now the data from the regular table needs to be imported into the partitioned table. How can this be done quickly?

| username: gary | Original post link

  1. insert select 2. dumpling backup and restore
| username: Hacker_6ASfgBFe | Original post link

I am using insert … select

| username: gary | Original post link

Do you think this method is relatively slow?

| username: 托马斯滑板鞋 | Original post link

Physical export and import are faster. If it’s a higher version, there’s also IMPORT INTO (which is simpler compared to Lightning). INSERT SELECT is much slower.

| username: gary | Original post link

You can try batch insert, this method will be faster.

| username: gary | Original post link

| username: caiyfc | Original post link

For example, batch can enable quick migration, but you didn’t mention your cluster version in your post. Without knowing the version you’re using, it’s impossible to provide effective assistance.

| username: 托马斯滑板鞋 | Original post link

Additionally, I checked the repository and it seems that future versions will support “import into from select,” greatly simplifying the complexity of data migration. :joy:

| username: 烂番薯0 | Original post link

insert into select * from t;

| username: gary | Original post link

The two mentioned earlier are not very complicated either :smile:

| username: Hacker_6ASfgBFe | Original post link

Mine is 5.4.

| username: caiyfc | Original post link

In that case, you can only rely on using Dumpling for export and Lightning for import. By the way, under normal usage, there’s no need to partition large tables unless you frequently need to delete large amounts of data periodically.

| username: TiDBer_5cwU0ltE | Original post link

CTAS fits the scenario you mentioned very well. Give it a try.

| username: YuchongXU | Original post link

Backup and Restore

| username: Hacker_6ASfgBFe | Original post link

It’s better to use insert…select. Exporting and importing large tables is hard to control. With insert…select, you can batch operations based on the time range field.

| username: 江湖故人 | Original post link

The fastest solution is to use Dumpling for export and Lightning for parallel import.

| username: Hacker_6ASfgBFe | Original post link

Yes, the large amount of data might affect the online environment.

| username: TiDBer_小阿飞 | Original post link

The speed is average, test environment.

| username: Hacker_6ASfgBFe | Original post link

Yes, this should be the slowest.