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

Questions

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

September 5, 2022

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?

>Solution :

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