Experience with SQL Server database importing MySQL database

Due to work needs, the data stored in the SQL Server database are imported into the MySQL database, collect relevant information on the Internet, find two ways, and now talk about their views respectively.
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 Server database
Data types such as ntext,image data can not be directly written to the MySQL database, it is said that as long as a little change can, but I did not think of the rookie how to change, second, because I in the MySQL database design in the time is set to int (save the time stamp), So after the data guide, there will be conflicts, again, this method generated MySQL data table 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 implement the data in the user table of the MYDB database in SQL Server into MySQL mydb database import
?
$CNX = Odbc_connect (' web ', ' admin ', ' 123456 ');//' web ' is the MyDB data source name 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 MyDB Database User table 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 ($cur)//Odbc_fetch_row from the user table in the SQL Server's MyDB library, if you select 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, where you can selectively select, 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); Here is the corresponding conversion of the DateTime type fields in SQL Server to the type I need int
$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;
}
?>

By saving the script as a sql.php and executing on the server, you can import the data from the user table of the MYDB database in SQL Server on the servers into the user table in the MyDB database in MySQL. The other table's operations are similar to this, do not repeat.

The following describes ASP script implementation SQL Server MYDB database data to MySQL mydb database import
<%
Set Conn=server.createobject ("Adodb.connection")
Conn.Open ' 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
Set Rs=server.createobject ("Adodb.recordset")
Sql= "Select Id,name,username,password,datediff (S, ' 1970-01-01 00:00:00 ', recdate) -8*3600,reid,filename, Filecontenttype,filevalue from Senddate "//This SQL statement implements converting a DateTime type of recdate field into a unix timestamp int type

Rs.Open sql,conn,1,3
Set Conn1=server.createobject ("Adodb.connection")
Conn1.open "Myoa", "root", "q1-d6=7?"
I=1
Do as not rs.eof
Field1 = RS (0)
Field2 = RS (1)
field3 = RS (2)
Field4 = RS (3)
Field5 = RS (4)
SQL1 = "INSERT into user (id,name,username,password,recdate)

VALUES ("&field1&", ' &field2& "', '" &field3& "', '" &field4& "'," &field5& ")"

Conn1.execute SQL1
Rs.movenext
I=i+1
Loop
Rs.close
Set rs=nothing
Conn.close
Set conn=nothing
Conn1.close
Set conn1=nothing

%>

The above two are respectively using PHP script and ASP script to the user table data from SQL Server to MySQL in the meantime I used 2 avoidance methods to avoid ntext,image type data transfer, one is to change the ntext field to nvarchar (4000 Because of the fact that the data in the original data length of the field is not more than 4,000 words, so there is no data truncation, another means is to remove the image type data to write to a file, save as a file, the file path to the database, method see below:

function Makeattach (filecontenttype,filevalue,i)
Select Case Filecontenttype
Case "Application/msword"
ext= "Doc"

Case "Application/vnd.ms-excel"
ext= "ExL"

Case "Application/vnd.ms-powerpoint"
ext= "PPS"

Case "Application/x-rar-compressed"
ext= "RAR"

Case "Application/x-zip-compressed"