MySQL to SQL Server Data Migration


By:
Overview

In this chapter we will discuss how to migrate your data from MySQL to SQL Server.

Explanation

For the mgiration we will use the SQL Server Import and Export Wizard.  Follow the steps below to migrate your data from a MySQL database to a SQL Server database.

  • Download and install the MySQL ODBC Connector. The latest release is available at  http://dev.mysql.com/downloads/connector/odbc/
  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. On the System DSN tab click 'Add', browse the 'MySQL ODBC driver' and click 'Finish'.
     
    image1

  • In the next dialog enter your MySQL server connection details, test it and click OK.
  • Set SQL_MODE to ANSI_QUOTES on MySQL Server.
  • Launch the SQL Server Management Studio
  • Create a new database or use an existing database
  • Right click on the target database and choose Tasks then click Import Data to launch the SQL Server Import and Export Wizard.
    • Select the .NET Data Provider for ODBC as Data Source and specify the DSN we created previously.
       
      image2

    • Select SQL Server Native Client 10 as Destination, enter the details of your SQL Server database and click Next.
    • Select Copy data from one or more tables or views and click Next.
    • Select the source and destination tables. You can enable identity insert using the Edit Mappings option.
    • Finally execute the package and save it for later use if necessary.





Comments For This Article




Sunday, September 12, 2021 - 5:20:35 AM - QinYin Huang Back To Top (89226)
It will be easier to use DMToolkit: https://www.dbsofts.com/articles/mysql_to_sql_server/

Tuesday, November 14, 2017 - 1:07:52 AM - Subham Back To Top (69702)

Will this solution work in real time also? I mean after set up once do I need to follow all procedure when any updation happen in Database? Do I need to all procedure every time?

 















get free sql tips
agree to terms