CODEWITHSUNDEEP
mysqlsqlsql-server
Andrew Florko
Andrew Florko

Reputation: 7750

How do I convert mySql SQL script to MS SQL one?

I have to migrate customer MySql database schema/data to MS SQL SERVER 2008. Finally I've received 70 Mb SQL file with mySQL dialect that is incompatible with MSSQL. 

DROP TABLE IF EXISTS `kladr`;
CREATE TABLE `kladr` (
  `id` int(11) NOT NULL DEFAULT '0',
  `idp` int(11) DEFAULT NULL,
...

It took a week of negotiations to receive such a file so I'm afraid to ask mySQL administrators to send me MS SQL SERVER compatible sql. Is there a way to convert mySQL dialect to MSSQL SERVER dialect automatically ?

Upvotes: 10

Views: 52580

Answers (4)

Jan
Jan

Reputation: 2273

You could (re)create the MySQL database from the MySQL scripts you received and use a database conversion tool. I have used FullConvert in the past and works excellent. Supports many databases and is very fast!

Upvotes: 0

Jerold Joel
Jerold Joel

Reputation: 251

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, queries, embedded SQL statements and SQL scripts from MySQL to Microsoft SQL Server.

http://www.sqlines.com/online

Upvotes: 6

Nanne
Nanne

Reputation: 64399

The easiest method would be to obtain a converter. There are multiple options available, but It might be difficult to find a good one: a lot of spammy options are available, all costing money ofcourse.

Microsoft has a manual online for migration: http://technet.microsoft.com/en-us/library/cc966396.aspx

You'll be needing some mysql tools and some mssql tools, stated on above link.

Adding a short exerpt:
If you check out the part called 'Using Data Loading'. The export file you allready have is probably fine, so you can go and skip 'Generating mysqldump Data Extract Scripts'.

Now the part you need:

Using the extracted scripts with SQL Query Analyzer
Generated scripts can now be used to create database objects and insert data. The preferred method to construct a database schema from MySQL scripts is to use the SQL Query Analyzer tool that is included with SQL Server 2000.
You can run SQL Query Analyzer directly from the Start menu, or from inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer from the command prompt by executing the isqlw utility.
In order for the script to correctly execute there is some additional work required, which involves certain changes with the SQL dialect. Also, remember to walk through the SQL script and change the data types to SQL Server compatible types. The diagram below shows an imported script from mysqldump, it is important to note that the dump is an ASCII script file.

Upvotes: 2

Sparky
Sparky

Reputation: 15075

There are a few ways to test whether a table exists in SQL. Here is one approach.

if object_id('<table>','U') is not null  drop <table>

So you would need to convert all of the 

DROP TABLE IF EXISTS `kladr`;

to that syntax. Note that there are other approaches to see if a table exists as well.

Also, MS-SQL doesn't use the ` (backtick) character, so you would need to get rid of all of them. However, some of the field/table names currently surrounded by backticks might be reserved words, in which case, you need to delimit those fields/tables with [fld_name] with brackets.

You might also run into some differences in the create table syntax, also the basic syntax is similar. The examples above would work in MS-SQL...

You can do it with script editing, but I would strongly suggest using the link suggested by Nanne above. Good luck

Upvotes: 0

Related Questions