2

When using MySQL Workbench to migrate data from SQL Server to MySQL I ran into a bug that it won't escape apostrophes. In the interim I would like to still port that data over. Is there any simple way to do this?

I've exported my data into a file with INSERT statements but they don't match the syntax of MySQL (I'm new to MySQL) so I don't know if it would work, neither does the file contain semicolons at the end of each statement.

Thanks in advance.

Update

So, far it looks like the easiest way to do it is to export the data into flat files and go through each table and make sure to also generate a SSIS file that you can use to automate future exports. Like so:

 DTExec /File MyExportedSissFileForOneTable.dtsx

And then use PowerShell or some other scripting language to do all the different files you need to update. Then import the files with MySQL - which I haven't figured out yet.

  • How big is the db you're trying to migrate? If it's sufficiently small enough you may be able to achieve this quicker by pushing the data via SQL Sever over a Linked Server connection. You could also look into SSIS, but that'll probably require more work than necessary regarding a scenario where you're moving away from MSSQL. – John Eisbrener Aug 17 '17 at 18:35
  • I have about 41 tables with the biggest table having about 91k records (most are much smaller). So, a pretty small database. This is all on my local machine. After working on this project for a year my boss decided he wanted MySQL instead of SQL Server :-( – Jon49 Aug 17 '17 at 18:46
  • I'm actually porting to MySQL not PostgreSQL – Jon49 Aug 17 '17 at 18:52
  • Gah! What's wrong with my eyes! Regardless, check Github to see if you can find some existing solutions that may make your life easier. Apparently I have db dyslexia... – John Eisbrener Aug 17 '17 at 19:01
1

In retrospect it is probably nicer to just roll my own converter since MySQL uses snake case instead of title case like SQL Server.

So, what I'm going to do is use a PowerShell script like so:

$tables = "FirstTable", "SecondTable"
function script {
    param(
        [System.String] $table
    )
    "set nocount on; select * from dbo."+$table
}

$tables |
% {
   $path = "C:\Data\"+$_+".txt"
   $script = script $_
   sqlcmd -S MyServer -d MyDatabase -W -h -1 -E -Q $script -s `"`t`" -o $path
}

This will export all the data from my SQL Server database and put them in files and then I will import those files into MySQL. Which I haven't quite figured out yet. I let the MySQL Workbench do the initial CREATE TABLE statements and used Vim to make everything snake case.

1

On SQL Server, bcp out.

On MySQL, LOAD DATA INFILE.

For moving data, have you looked at bcp? It's the "Bulk Copy Program" for SQL Server. https://docs.microsoft.com/en-us/sql/tools/bcp-utility. Set the row and column terminators to something not in your data ~EOC~ ~EOR~, etc.

You can use "LOAD DATA INFILE" on MySQL to bulk load flat files. Again, set your row and column terminators.
https://dev.mysql.com/doc/refman/5.7/en/load-data.html

I would use plain batch files for this, since I always had problems calling certain executables with PowerShell, usually when passing in arguments.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.