How to transfer data from MySQL to SQL Server

Home > Search > How-to
  by

Extract the tables and data from MySQL into a .SQL file. Follow the directions in the article on how to backup our MySQL or MariaDB database. This will create a file, such as example.sql. Open the file, and you will see blocks of SQL syntax. Following is an example of the type of syntax you should expect to see in the SQL file.

Changes need to be made to the .sql file, because there are differences between MySQL and Microsoft SQL Server. Do the following for the CREATE TABLE statement. The changes can be done in a text editor, such as Notepad or Notepad++.

  • Delete all of the ` (back ticks) characters
  • If int is followed by a a number in parathensis, delete the number in parathensis - example: int(11)
  • Update the ID column to be in this format: id int NOT NULL PRIMARY KEY IDENTITY (1,1)
  • Delete ENGINE=* DEFAULT CHARSET=*;

 

Do the following for the INSERT INTO statement.

  • Delete all of the ` (back ticks) characters
  • Delete all of the \\ characters
  • Change \\' (forward slash apostrophe) to '' (two apostrophes)
  • Replace 2014-01-01 with a date, such as 1969-01-01

 

 

Follow these steps to create the tables and data in Microsoft SQL Server, using the SQL file from MySQL.

  1. Sign into SQL Server using SQL Server Management Studio.
  2. In the top navigation bar, select New Query
  3. Copy the text in the example.sql file, and paste the text into the middle area of SQL Server Management Studio
  4. In the top navigation bar, select Execute

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments