Introduction to SQL Server data import MySQL database method

"Guide" This article describes two ways to import all data stored in a SQL Server database into a MySQL database.

The first is the installation of MySQL ODBC, SQL Server to use the export function, the choice of MySQL data source, direct data export, this method is simple, but for practical applications have many drawbacks, the most important manifestation is the data type problem, first of all, SQL Data types such as Ntext,image in the server database can not be directly written to the MySQL database, it is said that as long as a little change is possible, but the rookie has not thought how to change, second, because I in the MySQL database design will be set to int type ( Save is a timestamp), so after the data guide, there will be conflicts, and again, this method generated MySQL data table of the field types are not very appropriate, so this method I think can not be advocated.

The second is the use of PHP or ASP script to implement the data import function, this method needs to write a program, but the flexibility, the operation is not so difficult, everything in your grasp, is a brief introduction to this method. The prerequisite is your MySQL environment has been set up, first set up the target database, and then all the table structure with SQL statement generation, now everything has, only lack of data.

You can use the following PHP script to import data from the user table of the MYDB database in SQL Server to the MyDB database in MySQL:

?
$CNX = Odbc_connect (' web ', ' admin ', ' 123456 ');
' Web ' is the name of the MyDB data source in SQL Server.
' admin ' is the username to access mydb, ' 123456 ' is the password to access MyDB
$cur = odbc_exec ($CNX, ' select * from user ');
Open the user table for the MYDB database in SQL Server
$num _row=0;
$conn =mysql_pconnect ("localhost", "root", "123456");
Connect MySQL
@mysql_select_db (' MyDB ', $conn) or
Die ("Unable to connect to the database, please contact the Administrator!") ");
Open MySQL's mydb database
while (Odbc_fetch_row ($cur))
Extract the data from the user table in the SQL Server's MyDB library, and if you choose the data,
You can add conditional judgments to the previous SELECT statement
{
$num _row++;
$field 1 = Odbc_result ($cur, 1);
Here's the parameter I (1,2,3 ...) Refers to the first domain in the recordset,
You can choose selectively, fieldi get the value of the corresponding field, and then you can manipulate the Fieldi
$field 2 = Odbc_result ($cur, 2);
$field 3 = Odbc_result ($cur, 3);
$field 4 = Odbc_result ($cur, 4);
$field 5 = Odbc_result ($cur, 5);
$field 6 = Odbc_result ($cur, 6);
$field 5 = timetoint ($field 5); This is the type of datetime in SQL Server
The fields are converted to the int type I need.
$querystring = "INSERT INTO user
(id,name,username,password,recdate)
VALUES (' $field 1 ', ' $field 2 ', ' $field 3 ', ' $field 4 ', ' $field 5 ') ";
mysql_query ($querystring, $conn);
}
function Timetoint ($STR) {
$arr 1=split ("", $str);
$DATESTR = $arr 1[0];
$TIMESTR = $arr 1[1];
$arr _date=split ("-", $datestr);
$arr _time=split (":", $TIMESTR);
$year = $arr _date[0];
$month = $arr _date[1];
$day = $arr _date[2];
$hour = $arr _time[0];
$minute = $arr _time[1];
$second = $arr _time[2];
$time _int=mktime ($hour, $minute, $second, $month, $day, $year);
return $time _int;
}
?>