Here’s How You Can Copy a SQL Server Table to MYSQL Using PHP

Generally everybody has a basic way of proceeding at the working space. While working everyday the developers work on a planned job everyday. This includes pulling out the information’s out of Oracle database and then afterwards dumping them into Microsoft SQL table which is on one of the local IIS Web server. Then the team has in their hand a script on the remote LAMPP Web server that queries the MSSQL table and afterward shows results on the Web site.

However sometimes the results can be a bit undependable and slow as because of the different location of Web server and the MSSQL server. It becomes a situation of crisis sometimes when the network connection to IIS server or the server itself becomes down. In that scenario sometimes it is not even possible for the team to retrieve any data from the MSSQL sever.

While working on the scripts every developer I guess sometimes goes through certain critical situations. Generally there are certain tips for a guy who is working on a script to retrieve the information’s needed out of the MSSQL table to create afterwards a new table in the MYSQL server that resides on the same server of the website.

Firstly what needs to be done is to find out a way to get some information about the MSSQL table and its column, for doing that following query is helpful.

SELECT
COLUMN_NAME,

DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = ‘{$table_name}’

ORDER BY ORDINAL_POSITION ASC

This query in turn will put the name of the column, the data type and the maximum length of the column from the MSSQL table’s column itself.

After that one would want to build upon the queries that will create a new MySQL database and table. One should try these following while getting started in it:

$cols = array();

while($r = mssql_fetch_assoc($sql,$mslink)) {

$cols[$r[‘COLUMN_NAME’]] = $r[‘COLUMN_NAME’].’ ‘.$r[‘DATA_TYPE’];

$tmplen = trim($r[‘CHARACTER_MAXIMUM_LENGTH’]);

$cols[$r[‘COLUMN_NAME’]] .= (!empty($tmplen)) ? ‘(‘.$tmplen.’)’ : ”;

}

mssql_free_result($sql);

$sql = “CREATE DATABASE $dbname”;

if(mysql_query($sql,$mylink)) {

$sql = “CREATE TABLE $tname “;

$sql .= implode(“,n”,$cols);

/* If you have specific indices to add, you can add a line of code here to do that. */

$sql .= ‘)';

/* print(“We are preparing to run the following query to create the new table:n”.$sql); */

if(mysql_query($sql,$mylink)) {

print(‘We successfully created the table.’);

}

else {

print(‘There was an error creating the table. ‘.mysql_error());

}

}

else {

print(‘There was an error creating the database. ‘.mysql_error());

}

A new table has to be crated by now. But it should be noted that MSSQL does offer some data types that are much at a gap from those offered in MySQL. So therefore if someone’s MSSQL table includes any columns with data types that aren’t going with MySQL, they may feel the need to write a function to deal with that. For example, the MS SQL “currency” data type may feel the need of conversion to a decimal or float data type in the new MySQL table.

The following two tabs change content below.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>