How to convert query from phpMyAdmin SQL Dump to an sql server legible query

Question

Hi all,

I have undertaken a small project which already evolved a current database. The application was written in php and the database was mysql.

I am rewriting the application, yet I still need to maintain the database's structure as well as data. I have received an sql dump file. When I try running it in sql server management studio I receive many errors. I wanted to know what work around is there to convert the sql script from the phpMyAdmin dump file that was created to tsql?

Any Ideas?

Answer 1

phpMyAdmin is a front-end for MySQL databases. Dumping databases can be done in various formats, including SQL script code, but I guess your problem is that you are using SQL Server, and T-SQL is different from MySQL.

EDIT: I see the original poster was aware of that (there was no MySQL tag on the post). My suggestion would be to re-dump the database in CSV format (for example) and to import via bulk insert, for example, for a single table,

CREATE TABLE MySQLData [...]

BULK
INSERT MySQLData
FROM 'c:\mysqldata.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

This should work fine if the database isn't too large and has only few tables.

You do have more problems than making a script run, by the way: Mapping of data types is definitely not easy.

Here is an article about migration MySQL -> SQL Server via the DTS Import/Export wizard, which may well be a good way if your database is large (and you still have access, ie, not only have the dump).

Answer 2

MySQL's mysqldump utility can produce somewhat compatible dumps for other systems. For instance, use --compatible=mssql. This option does not guarantee compatibility with other servers, but might prevent most errors, leaving less for you to manually alter.

Answer 3

The syntax between Tsql and Mysql is not a million miles off, you could probably rewrite it through trial and error and a series of find and replaces.

A better option would probably be to install mysql and mysqlconnector, and restore the database using the dubp file.

You could then create a Linked Server on the SQL server and do a series of queries like the following:

SELECT * 
INTO SQLTableName
FROM OPENQUERY
(LinkedServerName, 'SELECT * FROM  MySqlTableName')