About data import from SQL Server database to MySQL database (zz)

To import all the data stored in the SQL Server database to the MySQL database, collect related information on the Internet, and find two methods. Now let's talk about them separately.

The first method is to install MySQL ODBC and use the export function of SQL Server to select the MySQL data source for direct data export. This method is simple, but there are many drawbacks for practical applications, the most important manifestation is the data type problem. First, in the SQL Server database

Ntext, image, and other data types cannot be directly written into the MySQL database. It is said that only a slight change is required. Unfortunately, this cainiao did not expect any change. Secondly, because the database design in MySQL sets the time to int type (the timestamp is saved), conflicts will occur after the data is imported, the Field Types of Mysql Data Tables generated using this method are not suitable, so this method is not recommended.

The second method is to use PHP or ASP scripts to import data.ProgramBut the flexibility is great, and the operation is not that difficult. Everything is under your control. Here is a brief introduction to this method.

The precondition is that your MySQL environment has been set up. First, you have created the target database and then generated all the table structures using SQL statements. Now everything is ready, and only data is missing.

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.
& lt ;?
$ CNX = odbc_connect ('web', 'admin', '000000'); // 'web' is the data source name of mydb in sqlserver, 'admin' is the username used to access mydb, and '123' is the password used to access mydb
$ cur = odbc_exec ($ CNX, 'select * From user '); // open the mydb Database User table in SQL Server
$ num_row = 0;
$ conn = mysql_pconnect ("localhost ", "root", "123456"); // connect to 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) // extracts data from the user table in the mydb database of SQL SERVER one by one. If you select the data, you can add conditions in the preceding SELECT statement.

{

$ Num_row ++;

$ Field1 = odbc_result ($ cur, 1); // here the parameter I (, 3 ..) it refers to the I-th field in the record set. You can select it selectively. fieldi gets the value of the corresponding field, and then you can perform operations on fieldi.

$ Field2 = odbc_result ($ cur, 2 );

$ Field3 = odbc_result ($ cur, 3 );

$ Field4 = odbc_result ($ cur, 4 );

$ Field5 = odbc_result ($ cur, 5 );

$ Field6 = odbc_result ($ cur, 6 );

$ Field5 = timetoint ($ field5); // converts datetime fields in SQL Server to the int type I need.

$ Querystring = "insert into user

(ID, name, username, password, recdate)

Values ('$ field1', '$ field2', '$ field3', '$ field4', '$ field5 ')";

Mysql_query ($ querystring, $ conn );

}

Function timetoint ($ Str ){

$ Arr1 = Split ("", $ Str );

$ Datestr = $ arr1 [0];

$ Timestr = $ arr1 [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;

}

? & Gt;

Save the script as SQL. php and execute it on the server to import the data in the User table of mydb database in SQL Server to the user table of mydb database in MySQL. The operations on other tables are the same as those in other tables.

The following introduces how to import data from the mydb database in SQL Server to the mydb database in MySQL.

& Lt; %

Set conn = server. Createobject ("ADODB. Connection ")

Conn. open 'web', 'admin', '200' // 'web' is the data source name of mydb in sqlserver, and 'admin' is the user name for accessing mydb, '123' is the password used to access mydb.

Set rs = server. Createobject ("ADODB. recordset ")

SQL = "select ID, name, username, password, datediff (S, '2017-01-01 00:00:00 ', recdate)-8*1970, Reid, filename, filecontenttype, filevalue from senddate "// This SQL statement converts the datetime type recdate field to the int type of UNIX timestamp.

Rs. Open SQL, Conn, 1, 3

Set conn1 = server. Createobject ("ADODB. Connection ")

Conn1.open "myoa", "root", "q1-d6 = 7? "

I = 1

Do while 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

% & Gt;

The above two methods use PHP scripts and ASP scripts to import data from the user table from SQL Server to MySQL. In this case, I use two methods to avoid ntext, for image data transmission, change the ntext field to nvarchar (4000). Because of the actual situation, the Data Length of this field in the original data cannot exceed 4000 characters, therefore, there is no data truncation. Another method is to extract the image data and write it to the file, save it as a file, and save the file path to the database. For details, see:

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 & lt; & gt; "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 & lt; & gt; "" 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 three 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, first, determine the suffix of the stored File Based on contenttype, then save the binary value to the file with the specified file name, and return the file name as an output parameter, write the returned parameters to the MySQL database and save them.

In a hurry, I would like to summarize the text here. I hope this text will be helpful to those who need it, so I will not take any detours. Thank you for your reading. :)

Posted on cold reading (37) Comment (0) EDIT alarm