MySQL to SQLServer [Express] 2008


Being a historical LAMP guy, the first problem is how do I get my data across?


The release of free SQLServer Express 2008, with the integration with Vista x64, PHP and Visual Studio is an attractive proposition. But: schema and data first.


1. Using PHPMyAdmin, I exported the tables as SQL; and specifically ANSI SQL:


mysql phpmyadmin export sql


 


 


 


 


 


 


2. Edit the SQL


This is the ugly part. There were four main changes:



  • Change syntax of the CREATE TABLE to place tables inside the correct namespace (eg: database.dbo.table) and a (ouch) find and replace on INSERTS

  • Changing the syntax of the PRIMARY KEY

  • Adding SQLServer Transact SQL (T-SQL) ‘go’ statements after the table creation and insertion blocks

  • Changing the column types to match SQLServer types

3. SQLCMD


SQLCMD is the command line interface to your SQLServer instance.


Simply typing SQLCMD –S MAJORSTAR\SQLExpress -E -i export.sql


Where SQLExpress is the instance on a machine called MAJORSTAR, -E to login with my current credentials and -i to run the export.sql file.


With further research, the -d (to set db name) and other like tweaking, the amount of editing as completed in step 2 could have been avoided.


 


 


 


 


 


 


 


 


In a few short steps, I had imported my schema and data into SQLServer Express 2008


For more indepth articles, The Guide to Migrating from MySQL to SQL Server 2005 looks informative

Comments (3)

  1. Prasanna Prabhu says:

    Why not try http://www.dbload.com/ works like a charm

  2. Last week, the SQLServer 2008 Express team released a download with the SQL Server Management Studio