If you are familiar with SQL then it is easy to convert the MySQL db to MSSQL db. The first thing which we have to do is we have to create a link between MySQL and SQL Server by using ODBC (Open Database Connectivity).
Steps to follow for creation of ODBC object:
- Open Control Panel.
- Click on Administrative Tools.
- ODBC Datasources(if your OS is 32-bit then select ODBC datasources (32 bit) else select ODBC datasources (64 bit)).
- Select System DSN tab.
- Click on Add button.
- Now a window with data sources will be displayed select MySQL ODBC from the list and click on Finish.
- Give the Datasource name as you wish and enter the TCP:IP of your MySql workbench. and select the Mysql db which you want to convert to MSSQL and click on Ok.
Here ends the initial Setup. Now we have to construct a bridge between the MySQL and MSSQL Server by using link providers in MSSQL Server.
Steps to follow for construction of Bridge(Link) between the MySQL and MSSQL Server.
- Open Sqlserver Management Studio and connect to the server.
- Open New Query window and paste the following code.
- USE [master]
- GO
- EXEC master.dbo.sp_addlinkedserver @server = N'ODBC ConnectionName', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=server_name; _
- DATABASE=db_Convert_to_MSSQL; USER=root; PASSWORD=your MySql pwd; OPTION=3'
- For confirmation whether the link between MYSQL and MSSQL Server is created just expand Server objects -> Linked Servers in Sql server management Studio.
Here ends the creation of bridge between MySQL and MSSQL Server. Now you have to create a database in SQL server and execute the query as follows.
- insert into sql database name
- select * from openquery (ODBC connection Name,''SELECT columns which you want
- from Mysqldatabase")