Migrate SQL DB to MySQL using a CSV file

MS SQL is a proprietary RDBMS from Microsoft and MySQL is a free and open-source RDBMS backed by Oracle. And with an increasing number of organizations transitioning from proprietary to open-source software, migration from MS SQL to MySQL database is gaining momentum.

This is not the actual way one should migrate but I simply avoided a lot of grey areas like —

1. Schema conversion
2. Data loss risk
3. Time consumption

Information about the database:

1. The table I was trying to migrate had more than 40 columns.
2. The table had all the standard delimiters that you can think of.
3. There were lots of NULL values all around the table.
4. Lastly, there were more than 1,00,000 rows.

Migration Steps:

1. Export the MS SQL table by going to tasks → export data…
2. Select Native Client as Source
3. Select Flat file as destination. Format delimited and code page as Latin I
4. In the next step, keep the default row delimiter and change the column delimiter to a rare ASCII value (a character that logically will not be present in real-life data)
5. Next, open MySQL Workbench and connect to the database. Before connecting, under the Advanced tab, add this option to the connector options in the others window: OPT_LOCAL_INFILE=1
6. Now execute the following command —

LOAD DATA LOCAL INFILE 'path\\to\\the\\file' INTO TABLE table_name
FIELDS TERMINATED BY '<rare ASCII Char>' 
LINES TERMINATED BY '\r\n';

This SQL statement load took 4–5 seconds and showed me an exclamation after executing. To verify the rows I had to do a count(*) on the table, but got the exact number of rows as MS SQL table.

This process saved a lot of time for me. However, there are some limitations and there are some scenarios where it is not possible to migrate all the tables.

But in case it fails let me know in the comments and I will connect with you and try to solve the issue. You can also connect me on LinkedIn below —

Kuharan Bhowmik

Thank you for making it to the bottom. I wish you all a Happy New Year 2021.