Select table from Sql server and insert data to Mysql table

Question

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

Have a look here http://stackoverflow.com/questions/15069962/php-pdo-insert-batch-multiple-rows-with-placeholders — Kevin Nagurski, May 01 '15 at 13:16
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 — 77120, May 01 '15 at 14:08
@77120 have you looked at my answer? It should complete all the inserts in about 1 minute. — Jonathan, May 01 '15 at 14:57

score 3 · Answer 1 · answered May 01 '15 at 13:30

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();
}

answered May 01 '15 at 13:30

Machavity

30,841
27
92
100

thanks for the improvement i did change it, but its still taking a lot of time (about 30min) – 77120 May 01 '15 at 13:42
Your best bet, then might be [load data infile](http://dev.mysql.com/doc/refman/5.1/en/load-data.html). It smokes writing SQL for large data sets – Machavity May 01 '15 at 13:51
You mean that i should first write the output from sqlserver to a file(csv) and then load that file into mysql, i think that should work – 77120 May 01 '15 at 14:01

score 2 · Accepted Answer · answered May 01 '15 at 17:32

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

score 0 · Answer 3 · answered May 01 '15 at 13:30

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.

answered May 01 '15 at 13:30

Alex

16,739
1
28
51

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. – Machavity May 01 '15 at 13:44
there is just one call in my answer. – Alex May 01 '15 at 13:45
Yes but if your SQL statement contains, say, 10000 rows it won't be faster. – Machavity May 01 '15 at 13:56
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: http://stackoverflow.com/a/3026146/4421474 – Alex May 01 '15 at 13:58

score 0 · Answer 4 · answered May 01 '15 at 14:06

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);

}

Select table from Sql server and insert data to Mysql table

4 Answers4

Linked

Related