-1

I have a running ms sql server and want some data copied to a mysql database.

i already can connect to them both so i made something like:

$pdo = new PDO('SQLSERVER', $user, $password);
$sql = "SELECT id, name FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();

$json_users = array();
while ($row = $stmt->fetchObject()){
   $json_users[] = $row;
}

$pdo = new PDO('MYSQLDB', $user, $password);

foreach ($json_users as $key => $value){
    $sql = "INSERT INTO users (id, name) VALUES (:id, :name)"
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam('id', $value->id, PDO::PARAM_INT);
    $stmt->bindParam('name', $value->name, PDO::PARAM_STR);
    $stmt->execute();
}

this does work but takes a lot of time cause its a big table.

so my question is can i insert the complete results from sqlserver to mysql at once with only one insert query? without the foreach?

Update: the table contains 173398 rows and 10 columns

Improve this question
3
  • Have a look here stackoverflow.com/questions/15069962/… Commented May 1, 2015 at 13:16
  • what version of mssql are you using? Commented May 1, 2015 at 13:43
  • Microsoft SQL Server Management Studio 10.50.1617.0 Microsoft Analysis Services Client Tools 10.50.1617.0 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 5.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.5466 Operating System 6.1.7601 Commented May 1, 2015 at 14:08

4 Answers 4

Reset to default
3

With prepared statements (especially for multi-insert) you want to have your prepared statement outside your loop. You only need to set the query up once, then supply your data in each subsequent call

$sql = "INSERT INTO users (id, name) VALUES (:id, :name)";
$stmt = $pdo->prepare($sql);
foreach($json_users as $key => $value){
    $stmt->bindParam('id', $value->id, PDO::PARAM_INT);
    $stmt->bindParam('name', $value->name, PDO::PARAM_STR);
    $stmt->execute();
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

You can export this into CSV file first from MSSQL then import that file into MySQL.

$pdo = new PDO('SQLSERVER', $user, $password);
$sql = "SELECT id, name FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();

$fp = fopen('/tmp/mssql.export.csv', 'w');
while ($row = $stmt->fetch(PDO::FETCH_NUM)){
   fputcsv($fp, array_values($row));
}
fclose($fp);
$pdo = new PDO('MYSQLDB', $user, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

$sql = <<<eof
    LOAD DATA LOCAL INFILE '/tmp/mssql.export.csv'
     INTO TABLE user_copy
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (id,name)
eof;
$pdo->exec($sql);

one drawback with the above, you need to have this configuration enabled in my.cnf ( MySQL configuration )

    [server]
    local-infile=1

Since MySQL cannot read files that are owned by others unless it it opened with --local-infile=1

Improve this answer

Comments

0

I would suggest not bind values, but generate the query string:

$sql = "";
foreach ($json_users as $key => $value){
    if ($sql=="") {
       $sql = "INSERT INTO users (id, name) VALUES ";
       $sql =." (".$value->id.',"'.$value->name.'")';
    } else {
       $sql .= ", (".$value->id.',"'.$value->name.'")';
    }
}

$stmt = $pdo->prepare($sql);
$stmt->execute();

It is not best practice, but since you trust data source it could help.

Improve this answer

4 Comments

This will be faster, but only up to a certain point. Then parsing what could potentially be a very large query becomes more cumbersome. Prepared statements never suffer from that because each data row is a separate call.
there is just one call in my answer.
Yes but if your SQL statement contains, say, 10000 rows it won't be faster.
why? it will be faster anyway. The only thing we should worry about - exceptions if any like too large query string that could be unacceptable: stackoverflow.com/a/3026146/4421474
0

Consider doing bulk inserts instead of one row at a time.

$sourcedb = new \PDO('SQLSERVER', $sourceUser, $sourcePassword);
$targetdb = new \PDO('MYSQLDB', $targetUser, $targetPassword);

$sourceCountSql = "SELECT count(*) count FROM users;";

/**
 * for mssql server 2005+ 
 */
$sourceSelectSql = "
SELECT
    id,
    name
FROM
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY id) RowNum,
            id,
            name
        FROM
            users
    ) users
WHERE
    RowNum >= %d
    AND RowNum < %d
ORDER BY
    RowNum
";

/**
 * for mssql server 2012+ 
 */
$sourceSelectSql = "
SELECT 
    FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT
    id,
    name
FROM
    users
ORDER BY
    id
OFFSET %d ROWS
FETCH NEXT %d ROWS ONLY        
";    

$sourceCount = $sourcedb->query($sourceCountSql, \PDO::FETCH_COLUMN, 0);

$rowCount = 1000;

$count = 0;
$count2 = 0;

for($x = 0; $x < $sourceCount; $x += $rowCount) {

    $sourceRecords = $sourcedb->query(sprintf($sourceSelectSql, $x, $rowCount), \PDO::FETCH_ASSOC);

    $inserts = [];

    foreach($sourceRecords as $row) {
        $inserts[] = sprintf("(:id_%1$d, :name_%1$d)", $count++);
    }

    $stmt = $targetdb->prepare(sprintf("INSERT INTO users (id, name) VALUES %s;", implode(',', $inserts));

    foreach($sourceRecords as $row) {
        $stmt->bindParam(sprintf('id_%d', $count2), $row['id'], \PDO::PARAM_INT);
        $stmt->bindParam(sprintf('name_%d', $count2), $row['name'], \PDO::PARAM_STR);
        $count2++;
    }

    $targetdb->execute();

    unset($inserts);

}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.