What will be the best practice to fetch data from MSSQL and insert/update to MySQL?

Question

So, i'd like to fetch data from MSSQL and insert/update in MySQL.

I have this script:

//QUERY
$sql = "SELECT id, ordernr FROM orders";

//EXECUTE
$stmt = sqlsrv_query( $conn, $sql );

//ERROR?
if( $stmt === false) {
  die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {}

Should i create an array and insert whole the array in MySQL, is that possible?
Or should i iterate the fetched array and insert it into MySQL row by row?

I have this in mind:

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
  $query = "INSERT INTO table
            SET ordernr = $row['ordernr']
            ON DUPLICATE KEY UPDATE
            ordernr = $row['ordernr']";

  EXECUTE $qurey... bla bla
});

What will be the best way?

The right solution depends on the number of rows the select query returns. In case of thousands of rows, exporting the results into CSV and then loading the CSV with load data infile may yield better performance then a looped insert. Anyway, see the answers to the duplicate question for implementation guidelines. — Shadow, Sep 05 '22 at 06:54

score 1 · Accepted Answer · answered Sep 05 '22 at 06:44

Use a prepared statement that you prepare once, outside the loop.

$stmt = $conn->prepare("
    INSERT INTO TABLE (id, ordernr) 
    VALUES (?, ?) 
    ON DUPLICATE KEY UPDATE ordernr = VALUES(ordernr)");
$stmt->bind_param($id, $ordernr);

while ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    $id = $row['id'];
    $ordernr = $row['ordernr'];
    $stmt->execute();
}

What will be the best practice to fetch data from MSSQL and insert/update to MySQL?

1 Answers1