Transfert de données de MSSQL à MySQL

L’équipe de MySQL a créé une page pour répondre à la question de comment convertir une base de données de Microsoft SQL Server vers MySQL.
Pour plus d’information :

 dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

Pour ma part, les programmes proposés n’ont pas voulu de mes bases de données phpBB. J’ai donc créé le script ci-dessous pour réimporter les messages dans la nouvelle base de données.

<?php
define('OUTPUT', 'MySQL'); // values: MySQL or MSSQL
 
$db       = 'database';
$username = 'username';
$password = 'password';
 
$mssql_link = mssql_connect('localhost', $username, $password);
mssql_select_db($db, $mssql_link);
 
function get_insert_statement($table, $columns, $types, $data) {
    $insert = "INSERT INTO `$table` (`" . join($columns, '`, `')
        . "`) VALUES (";
 
    for ($i = 0; $i < count($data); ++$i) {
        if ($i > 0) $insert .= ', ';
        if ($types[$i] == 'int') {
            $insert .= $data[$i];
        } else {
            $insert .= "'" . str_replace("'",
                (OUTPUT == 'MSSQL' ? "''" : "\\'"), $data[$i]) . "'";
        }
    }
 
    $insert .= ');';
    return $insert;
}
 
// Main program
 
echo '<pre>';
 
$query = 'SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS';
 
$tables = mssql_query($query);
 
while ($table = mssql_fetch_array($tables, MSSQL_NUM)) {
    $table = $table[0];
 
    // Skip system tables
    if (preg_match('/^(sys|syn|dtproperties)/', $table)) continue;
 
    echo "\n--\n-- $table\n--\n\n";
 
    // Retrieve column names for current table
    $query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS "
        . "WHERE TABLE_NAME = '$table'";
    $res = mssql_query($query);
    $columns = array();
 
    while ($column = mssql_fetch_array($res, MSSQL_NUM)) {
        array_push($columns, $column[0]);
    }
 
    mssql_free_result($res);
 
    // Retrieve field type of each column
    $query = "SELECT TOP 1 " . join($columns, ', ') . " FROM $table";
    $res = mssql_query($query);
    $types = array();
 
    for ($i = 0; $i < count($columns); ++$i) {
        array_push($types, mssql_field_type($res, $i));
    }
 
    mssql_free_result($res);
 
    // Create the INSERT INTO statements for current table data
    $query = "SELECT " . join($columns, ', ') . " FROM $table";
    $res = mssql_query($query);
 
    while ($data = mssql_fetch_array($res, MSSQL_NUM)) {
        $insert = get_insert_statement($table, $columns, $types, $data);
        echo htmlentities($insert) . "\n";
    }
 
    mssql_free_result($res);
}
 
mssql_free_result($tables);
 
echo '</pre>';
 
mssql_close($mssql_link);
?>
Flattr