I'm using a PHP script to transfer mySQL records in one table one Server A to another table (same table definition) on Server B. I cannot overwrite the existing data in the table on Server B. There are no keys to worry about. The way I'm doing it (see below) takes too long for higher volumes (10K - 100K records). Can anyone describe a fast, efficient way to do this?
If it is faster to do something directly in mySQL, please describe how to do that.
Thanks!
<?php
# connect to server A
if (!$cxnA = mysqli_connect($serverA, $user, $pw, $db))
{
die("Could not connect to MySQL server A");
}
# connect to server B
if (!$cxnB = mysqli_connect($serverB, $user, $pw, $db))
{
die("Could not connect to MySQL server B");
}
$sql= "SELECT *
FROM tableA";
if (!$result1 = mysqli_query($cxnA,$sql))
{
die("error...");
}
$num1 = mysqli_num_rows($result1);
if( $num1 == 0 )
{
echo "no records<br/>";
}
else
{
echo $num1." records<br/>";
while($row1 = mysqli_fetch_assoc($result1))
{
extract($row1);
$sql="
INSERT INTO tableB (
field1,
field2
)
VALUES (
'$field1',
'$field2'
)";
if (!$result2 = mysqli_query($cxnB,$sql))
{
echo "error inserting record<br/>";
}
}
}
?>