MySQL and MS SQLServer both can be accessed using SQL, but if you look closely at both SQL dialects, you’ll find a lot of differences.
Backups of MySQL databases are created using the utility mysqldump, which simply dumps all the SQL statements required to recreate the database into a SQL file, as opposed to SQLServer backup, which creates a binary file representing the database contents.
As we are interesting in parsing the SQL and converting it into valid T-SQL statements, we have to look at the differences between the SQL dialects. Fortunately, since the SQL files are output by a tool, the generated statements are fairly regular.
Here are some special features of the MySQL dialect, which need to be handled for conversion into T-SQL:
- Identifiers are quoted with a backtick `, rather than square brackets
- IDENTITY columns are marked by the auto_increment keyword
- Binary columns may store text values
- Text values may contain character codes with leading backslash \, as known from C# or C
- Values for DATETIME, DATE and TIME columns need not be quoted as strings
- ‘0000-00-00’ is a valid DATE
- A CREATE TABLE statement may include the definition of indexes, unique indexes and fulltext indexes
- Both SQL engines provide support for collations, but they are completely different
I have previously written about converting MySQL SQL into T-SQL manually, but having a tool perform the repetitive actions would be very nice 😉
Pingback: Importing mysqldump into MS SQL Server | devioblog