MySQL to SQLServer [Express] 2008

  • Article

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