SQL / MySQL

mizzy posted this at 14:28 — 12th June 2002.

Joined: Jun 2001

Hi all,
I would like to find out, can I transfer MySQL data as is to an SQL server?
If not what do I have to change? I was developing a site for someone and it has a MySQL backend but it so happens that their host only wants to use MS SQL server.

Thanks
Mizzy

mairving posted this at 14:57 — 12th June 2002.

They have: 2,256 posts

Joined: Feb 2001

If you are transferring the data, you can run a select statement from MySQL like:

<?php
SELECT * from table_name into outfile \"/home/username/tablename.txt\"
?>

This will only export the data as a space delimited .txt file which can be imported into SQL Server. I am not sure about the import part but I am sure that Peter can tell you.

Note: You will have to export every table.

Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states

Peter J. Boettcher posted this at 15:49 — 12th June 2002.

They have: 812 posts

Joined: Feb 2000

Unfortunately I don't think there is a native import filter for mySQL --> MSSQL.

So you'll have to follow Mark's advice and export your tables into text files, then create the empty table(s) in MSSQL and import data into them using the text filter and your text files. With Enterprise Manager this is easy, just right-click on your database and select import, then for the datasource select text, and select the file. Just follow the directions after that, it's really simple.

PJ | Are we there yet?
pjboettcher.com

ROB posted this at 16:15 — 13th June 2002.

They have: 447 posts

Joined: Oct 1999

could you not just use mysqldump to dump the table as sql commands and use that to create the MS SQL database?

i'm not sure if mysqldump dumps mysql specific functions or not... it may be worth a try though... even though, microsoft has a problem following standards so even ansii sql may not import correctly into a microsoft database.