Port SQL Server data to MySQL without Workbench

Question

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.

Answer 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.

Answer 2

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://learn.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.