Being a historical LAMP guy, the first problem is how do I get my data across?
1. Using PHPMyAdmin, I exported the tables as SQL; and specifically ANSI 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
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