Convert MySQL backup (dump script) to MS SQL?

Question

I have a mysqldump output of a fairly large (by measure of doing things manually) database saved in a text file. Does anybody know of a (free) way to use this to build a database in MS SQL? I'm specifically using SQL Server 2008.

Answer 1

I used something like this:

-- Path/Filename of inputfile for later use
declare @Out varchar(100)
set @out = 'c:\data\Test\TBL1245.dat'

-- Define a structurefile based on a predefined target-table for later use:
exec master..xp_cmdshell  'bcp DB4711.dbo.[TBL1245] format nul -t\t  -c -x -f "c:\data\Test\Config\TBL1245.xml" -T'

-- initialie the targettable:
truncate table   DB4711.dbo.[TBL1245]

-- Load the data:
INSERT DB4711.dbo.[TBL1245] 
exec('SELECT * 
FROM OPENROWSET (
    BULK ''' + @out + '''
    , FORMATFILE = ''c:\data\Test\Config\TBL1245.xml''
    , ROWS_PER_BATCH = 100
) as co_in
where KeyFLD is not NULL') -- a condition may be set here if needed

Assuming you have a table (TBL1245) in a database (DB4711) to hold all the fileds of your datadumo-file. if you have to change delimiter see in ms-bol for the bcp parameters.

Answer 2

You could reload that data back into MySQL and execute mysqldump using the following:

mysqldump -h... -u... -p... --compatible=mssql > MSSQL_Compatible_Data.sql

Here is what the mysqldump --help --verbose says on that

--compatible=name Change the dump to be compatible with a given mode.
By default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.

Give it a Try !!!