CODEWITHSUNDEEP
mysql
Rahul
Rahul

Reputation: 18557

How to copy data from one table to other in same database?

In MySQL how to copy data from one table to another in the same database table?

I know insert into select, but it is taking forever to do this, especially on a live database, we can't take a risk.

Some conditions are there:
1. table1 is a source table and table1_archives is a destination table.
2. table1_archives already have data so we can only append.

My attempt: 

time mysqldump --log-error=$logfile --complete-insert --insert-ignore 
--no-create-info --skip-triggers --user=$dbuser --host=$host $dbname table1  
--where="created < now()-interval 10 month" > $filename

But it has the name of table1, so I can't insert it into table1_archives.

Any guidance will be appreciated.

Thanks in advance.

Upvotes: 0

Views: 64

Answers (2)

ascsoftw
ascsoftw

Reputation: 3476

In your output file, you need to change the table name table1 to table1_archives. Unfortunately mysqldump does not have any way to do this. You will have to do this on the fly using sed, which will rename everything in output file from table1 to table1_archives.

Since your columns can also contain the content like table1, its better to search and replace by enclosing them in backticks.

You can also use gzip to compress the output file.

Here is the command that worked for me

mysqldump -u USER -h  HOST -p --skip-add-drop-table --no-create-info --skip-triggers --compact DB table1 |\
  sed -e 's/`table1`/`table1_archives`/' | gzip >   filename.sql.gz

Upvotes: 1

Bilal Siddiqui
Bilal Siddiqui

Reputation: 3629

"but it is taking forever to do this"

There is a small trick to avoid this and then insert into will work faster:

Insert into table1 select * from table2

Trick:

step-1: drop all indices from table2
step-2: execute query
step-3: create indices again

Upvotes: 1

Related Questions