Talk about data importing MySQL database from SQL Server database Experience (original)

mysql|server| Data | database | Original due to the work needs, to be stored in the SQL Server database all the data into the MySQL database, collect relevant information on the Internet, find two ways, 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"
ext= "Zip"

Case "Image/gif"
ext= "GIF"

Case "Image/pjpeg"
ext= "JPG"

Case "Text/plain"
ext= "TXT"

Case Else
ext= "X"

End Select
If ext<> "X" Then
Set Fso=server.createobject ("FileSystemObject")
Fname= "Attech" &i& "." &ext
Dir= "D:\attach\"
If FSO. FileExists (dir & fName) Then fso.deletefile Dir & FName
If fname<> "" and not FSO. FileExists (Dir & FName) Then
Set strm1=server.createobject ("ADODB. Stream ")
Strm1. Open
Strm1. Type=1 ' Binary
Strm1. Write Filevalue
Strm1. SaveToFile Dir & fname,2
Set strm1=nothing
End If
Makeattach=fname
End If
End Function

This function has 3 input parameters, the first is the contenttype of the file, the second is the binary value of the file, and the third is a variable that can distinguish the file name, and then the name of the file to be stored according to the ContentType, then the binary value is saved to the file with the specified file name. The file name is returned as an output parameter and the returned parameter is written as data to the MySQL database for saving.
Time in a hurry, summed up here, I hope that these words for people who need some help, less to take detours, thank you for your reading. ：）