Transefering huge data from one table to another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eskio
Forum Commoner
Posts: 66
Joined: Tue Apr 01, 2008 1:00 am

Transefering huge data from one table to another

Post by eskio »

Hi,
I was transferring data from one table of a database to a table in another database. Both databases are stored in MySQL server. But the query only execute around 2,5000 records and stops without any error.
My first table contains around 64,000 records.
Can any one suggest how I can transfer all the records without any interruption and inform me why the interruption is occurring?

Thanks
User avatar
ghurtado
Forum Contributor
Posts: 334
Joined: Wed Jul 23, 2008 12:19 pm

Re: Transefering huge data from one table to another

Post by ghurtado »

How are you running the query? (PHP, mysql command line, phpMyAdmin, etc..)
eskio
Forum Commoner
Posts: 66
Joined: Tue Apr 01, 2008 1:00 am

Re: Transefering huge data from one table to another

Post by eskio »

I had written a PHP code to transfer the data.

Code: Select all

$Link1 = 'Connection to first DB';
$Link2 = 'Connection to 2nd DB';
$query = "SELECT * FROM TableName ";
$result = @mysql_query($query,$Link1) or die("Error: ".mysql_error());
while($rowdata = @mysql_fetch_array($result)){
    $value1=$rowdata['XXX'];
    $value2=$rowdata['XXX'];
    $query_insert = "INSERT INTO TableName2 (id,code,description) VALUES($value1, '$value2' ,'') ";
    @mysql_query($query_insert,$Link2) or die("Error ".mysql_error());  
} //while($rowdata = @mysql_fetch_array($result))           
print "Query executed successfully!!<hr>";
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Transefering huge data from one table to another

Post by Bill H »

Your script is probably timing out. Do you have access to run phpmyadmin?
eskio
Forum Commoner
Posts: 66
Joined: Tue Apr 01, 2008 1:00 am

Re: Transefering huge data from one table to another

Post by eskio »

I have access to run phpmyadmin, but when I transfered data I have to use base64_encode() and md5() functions. That is why I did not use phpmyadmin.

How can I fix the timeout?
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Transefering huge data from one table to another

Post by Apollo »

Process it in chunks, e.g. let the script process 100 entries at a time, and then call itself (with &start=100) using a meta refresh to do the next 100, etc.

Dirty, but it works, and you get to see how far it is in the process while it's busy.
eskio
Forum Commoner
Posts: 66
Joined: Tue Apr 01, 2008 1:00 am

Re: Transefering huge data from one table to another

Post by eskio »

thanks your the idea.

Code: Select all

$LIMIT1=0;
for ($i=0; $i<=65; $i++){
  $LIMIT1 += $i*1000;
  $query = "SELECT * FROM TableName LIMIT $LIMIT1, 1000";
  .............
  .............
} // for
$i<=65 because I have over 64000 records
devendra-m
Forum Contributor
Posts: 111
Joined: Wed Sep 12, 2007 3:16 am

Re: Transefering huge data from one table to another

Post by devendra-m »

How can I fix the timeout?
call set_time_limit(0); at the top of your page.
Post Reply