A. Installing MySQL ODBC driverInstall the CONNECTOR/ODBC driver for MySQL. One thing to note here is that the CONNECTOR/ODBC driver corresponds to the version of MySQL server.
two. Create a System DSNDSN defines an established database for ODBC and the ODBC driver that must be used. Each ODBC driver defines the information required to create a DSN for a database that is supported by the driver. To create a System DSN, proceed as follows: Start-up settings, Control Panel, management tools, data Source (ODBC), open ODBC Data Source Administrator by this process. Switch to the System DSN tab and click the Add button. Pop up the Create New Data Source dialog box, select the MySQL ODBC 5.3 Unicode driver Driver (depending on the version, may have a different name), click the Finish button. Set MySQL Database account information in the Link MySQL dialog box that pops up. The key is several parameters under the Login tab. The data source name, which is arbitrarily named according to the naming rules, will eventually appear in the list under the System DSN tab in ODBC data Source manager. L Description, the description of the data source, not fill in. L Server,mysql Server host name, here to fill in the computer hostname or localhost can be. User and password are the username and password corresponding to MySQL server. L database, select the databases that the data source points to. In this case, you must require that the previous parameters are correct, otherwise you will be prompted with an error and you cannot select a database in MySQL server. There are also two parameters that need to be noted are the port and character Set under the Connect Options tab. Port is used to set the communication port for MySQL server, which defaults to 3306, which can not be set if the default port is not changed at installation time. The Character set is used for setting the database language encoding, where GBK is selected. Click the OK button to complete the creation of the System DSN, return to the ODBC Data Source Administrator dialog box, and under the System DSN tab, you can view the data source that you just created. Click the OK button to exit.
three. Create a link service for MSSQL to MySQLOpen SQL Server Management Studio and run the following statement to establish a linked server with the MySQL server through the previously created ODBC data source.
EXECsp_addlinkedserver@server = 'mysqltest',@srvproduct='MySQL',@provider = 'Msdasql',@datasrc = 'Database name'GOEXECsp_addlinkedsrvlogin@rmtsrvname='mysqltest',@useself='false',@locallogin='SA',@rmtuser='Root',@rmtpassword='Root User Password'GO
Refresh the next linked server node to see the link created above with the statement above.
Four. Using SQL statements to transfer data to MSSQLCreate a new database in Microsoft SQL Server and run the following statement to import the MySQL database "Tigerdb" into the Microsoft SQL database "Testmysql" after running.
SELECT * into database. dbo. Table from OPENQUERY (Mysqltest,'select * from database. dbo. Table ' )
This completes the database migration from MySQL server to MS SQL Server.
PS: End again wordy two nonsense, last night began to use the MySQL, from the installation to use, feeling still a little annoying, the view of the operation habits, suddenly with the command line feel good trouble. I have to sigh that Microsoft's packaging is really good, the user's habit of training up. For a qualified IT staff, most of Microsoft's software can be said to be a fool-type operation, do not look at the description of the whole can be run perfectly.
MySQL data transfer to MSSQL detailed
