How to import data from an sqlserver database to a mysql database

Let's talk about how to import data from an SQL server database to a mysql database to import all the data stored in the SQL server database to a mysql database. we will collect relevant information online, find two ways to talk about them.

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, ntext, image, and other data types in the SQL server database cannot be directly written into the mysql database. it is said that only a slight change is required, unfortunately, this Cainiao did not think about how to change it. Secondly, because I set the time in the mysql database design to the int type (the timestamp is saved), after the data is imported, conflicts will occur. Again, the field type of the mysql data table generated by this method is not suitable, so this method is not recommended.

The second method is to use PHP or ASP scripts to import data. this method requires programming, but it is flexible and difficult to operate. everything is under your control, this method is briefly introduced.

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 mydb database in SQL server to mydb database in mysql.

$ Cnx = odbc_connect ('web', 'admin', '000000'); // '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.
$ Cur = odbc_exec ($ cnx, 'select * from user'); // open the user table of mydb database 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;
}
?>

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.

<%
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.