Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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