Convert MySQL to MS Sql Server February 7
I’ve been setting up some small database-driven sites lately and I needed to convert some MySQL tables to MS Sql Server. It’s not too hard convert them by hand, but it is oh so tedious…
So I wrote a small app to convert the MySQL tables to MS SQL, which you can try out here:
Now this was a quick & dirty app, so it really doesn’t do much else other than convert the datatypes and get the syntax right. It doesn’t convert keys/constraints/indexes or any of that stuff. It doesn’t even convert all the datatypes, but it has the main ones (ie - the ones I needed) covered. Note: If you add more datatypes or add support for indexes etc just email/post your changes and I will update this code as well as giving link credit.
Here is the code to do the conversion:
Private Function ConvertTable(ByVal sMySQL As String, _
ByRef sMSSQL As String) As String
Dim sLine As String = ""
Dim iC As Integer
Dim sVarName As String
Dim sDatatype As String
Dim sLen As String
Dim iLen As Integer
Dim sNewDataType As String = ""
Dim sOutput As String = ""
Dim iLen2 As Integer
For iC = 0 To sMySQL.Split(vbLf).GetUpperBound(0)
sLine = sMySQL.Split(vbLf)(iC)
sLine = Trim$(sLine)
If sLine.StartsWith("CREATE TABLE") = True Then
sMSSQL = sMSSQL & sLine & vbCrLf
ElseIf sLine.StartsWith("KEY") = True Or sLine.StartsWith("PRIMARY KEY") = True Then
'Ignore this
ElseIf sLine.StartsWith(")") = True Then
sMSSQL = Strings.Left$(sMSSQL, Len(sMSSQL) - 3)
sMSSQL = sMSSQL & vbCrLf & ")"
Exit For
Else
'sLine = sLine.Replace(",", " ")
sVarName = sLine.Split(" ")(0)
sDatatype = sLine.Split(" ")(1)
If InStr(sDatatype, "(") > 0 Then
sLen = Mid$(sDatatype, InStr(sDatatype, "("))
sLen = sLen.Replace("(", "").Replace(")", "")
If InStr(sLen, ",") > 0 Then
iLen = Val(sLen.Split(",")(0))
iLen2 = Val(sLen.Split(",")(1))
Else
iLen = Val(sLen)
End If
sDatatype = Strings.Left$(sDatatype, InStr(sDatatype, "(") - 1)
End If
If InStr(sDatatype, ",") > 0 Then
sDatatype = sDatatype.Split(",")(0)
End If
Select Case LCase(sDatatype)
Case "int", "smallint", "year", "tinyint"
sNewDataType = "int"
Case "blob", "text"
sNewDataType = "text"
Case "decimal"
sNewDataType = "decimal(" & iLen & "," & iLen2 & ")"
Case "double"
sNewDataType = "float(10)"
Case "date", "datetime"
sNewDataType = "datetime"
Case "timestamp"
sNewDataType = "bigint"
Case "varchar"
sNewDataType = "varchar(" & iLen & ")"
Case "char"
sNewDataType = "char(" & iLen & ")"
Case Else
sOutput = sOutput & "Unknown datatype = '" & sDatatype & "'"
sMSSQL = ""
Exit For
End Select
sMSSQL = sMSSQL & sVarName & " " & sNewDataType & "," & vbCrLf
End If
Next
If sOutput = "" Then
sOutput = "Conversion Complete"
End If
ConvertTable = sOutput
End Function
Note: I have now made another post which shows how to Convert MySQL Inserts to MS SQL Server
Lee Francis Wilhelmsen Feb 10
Probably useful, but why would you want to?
To keep things unbiased you might want to also supply to opposite operation, MS SQL -> MySQL.
Gath Feb 10
Hi Lee,
I needed to convert an existing MySQL database of test data to MS SQL Server so that I could use it for an app that I was writing.
Cheers,
Gath
Scott Swank Feb 10
Honestly, MS SQL Server is more or less my last choice from among rdbms offerings. It costs a fair bit and contains a fair number of unfortunate architectural decisions. It has just recently gotten around to MVCC (which MySQL, PostgreSQL and of course Oracle have had for a long time), and MS suggests not necessarily using MVCC in its current implementation for performance reasons. Its optimizer commonly constructs sub-optimal execution plans. It stores locks in memory, so it engages in lock escalation and cannot share locks (and hence cannot cluster). Oh, and t-sql is about mature a programming language as ms-dos batch scripting.
If anything, work to convert away from MS SQL Server where you can.
tim Feb 21
I’m not an MS basher like some others (it’s a fine rdbms), but I would love a tool that went the other way (I like MySQL as well… can’t we all just get along?). I bet you’d get lot’s of hits if it went both ways : ) Just my plug, since I’m needed to do that very thing right now.
thanks.
Gath Feb 21
Hi Tim,
An excellent resource for converting MS SQL to MySQL can be found here:
http://www.kofler.cc/mysql/mssql2mysql.html
Cheers,
Gath
Tim Mar 11
Sweet little app! Very useful.
Rabbi May 2
Server Error in ‘/BlogDemos’ Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a tag within a “web.config” configuration file located in the root directory of the current web application. This tag should then have its “mode” attribute set to “Off”.
Notes: The current error page you are seeing can be replaced by a custom error page by modifying the “defaultRedirect” attribute of the application’s configuration tag to point to a custom error page URL.
Marcus May 3
Any chance you could do a similar utility from MSSQL to MYSQL?
The utility Gath posted is not quite as user friendly.
Thanks in advance.
Kristof May 18
i’m converting mysql > mssql
how to convert these mysql columns into mssql?
1) `label` enum(’operation’,'maintenance’,'technical’,'certificates’,’safety’,'transport’,’storage’) NOT NULL default ‘operation’,
2) `language_id` set(’1′,’2′,’3′,’4′,’5′) NOT NULL default ‘1,2,3,5′,
thx a lot
kristof
Kurt May 29
I found the need to modify this code for my conversion of a MySQL database. I added an extra check to ignore “FULLTEXT KEY” and ignore blank lines, as well as to continue through multiple tables. It also replaces “ with [] on field names. My code below is the code-behind of a asp.net page to process a file locally and display the results. I also modified some of the data types and added some new ones according to this: http://technet.microsoft.com/en-us/library/cc966396.aspx. You may or may not find it useful.
Imports System.IO
Imports System.Text.RegularExpressions
Partial Class temp_Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Dim sMySQL As String = File.ReadAllText(Server.MapPath(”~/temp/mysql-script.txt”))
Dim sMSSQL As String = String.Empty
Dim output As String = String.Empty
output += ConvertTable(sMySQL, sMSSQL)
Response.Write(”Conversion Results”)
Response.Write(output)
Response.Write(”MS SQL Code”)
Response.Write(sMSSQL)
Response.Write(”")
End Sub
Private Function ConvertTable(ByVal sMySQL As String, _
ByRef sMSSQL As String) As String
Dim sLine As String = “”
Dim iC As Integer
Dim sVarName As String
Dim sDatatype As String
Dim sLen As String
Dim iLen As Integer
Dim sNewDataType As String = “”
Dim sOutput As String = “”
Dim iLen2 As Integer
Dim sLines() As String = sMySQL.Split(vbLf)
For iC = 0 To sLines.GetUpperBound(0)
sLine = sLines(iC)
sLine = Replace(Replace(Trim$(sLine), vbCr, “”), vbLf, “”)
If String.IsNullOrEmpty(sLine) Then
‘ do nothing
ElseIf sLine.StartsWith(”CREATE TABLE”) = True Then
sMSSQL = sMSSQL & Regex.Replace(sLine, “`([^`]*)`”, “[$1]“) & vbCrLf
ElseIf sLine.StartsWith(”KEY”) Or sLine.StartsWith(”PRIMARY KEY”) _
Or sLine.StartsWith(”FULLTEXT KEY”) Then
‘Ignore this
ElseIf sLine.StartsWith(”)”) = True Then
sMSSQL = Strings.Left$(sMSSQL, Len(sMSSQL) - 3)
sMSSQL = sMSSQL & vbCrLf & “)” & vbCrLf & vbCrLf
‘Exit For
Else
sVarName = sLine.Split(” “)(0)
sDatatype = sLine.Split(” “)(1)
If InStr(sDatatype, “(”) > 0 Then
sLen = Mid$(sDatatype, InStr(sDatatype, “(”))
sLen = sLen.Replace(”(”, “”).Replace(”)”, “”)
If InStr(sLen, “,”) > 0 Then
iLen = Val(sLen.Split(”,”)(0))
iLen2 = Val(sLen.Split(”,”)(1))
Else
iLen = Val(sLen)
End If
sDatatype = Strings.Left$(sDatatype, InStr(sDatatype, “(”) - 1)
End If
If InStr(sDatatype, “,”) > 0 Then
sDatatype = sDatatype.Split(”,”)(0)
End If
Select Case LCase(sDatatype)
Case “bigint”, “datetime”, “timestamp”, “text”, “real” ‘ types that are the same
sNewDataType = sDatatype
Case “tinyint” ‘ integer types
sNewDataType = “tinyint”
’sNewDataType = “smallint” signed tinyint’s should go to smallint instead
Case “smallint”
sNewDataType = “smallint”
Case “int”, “mediumint”, “integer”
sNewDataType = “int”
Case “tinytext” ’string types
sNewDataType = “varchar(256)”
Case “text”, “mediumtext”, “longtext”
sNewDataType = “text”
Case “varchar”
sNewDataType = “varchar(” & iLen & “)”
Case “char”
sNewDataType = “char(” & iLen & “)”
Case “tinyblob” ‘blob types
sNewDataType = “binary(256)”
Case “blob”
sNewDataType = “varbinary(max)”
Case “mediumblob”, “longblob”
sNewDataType = “image”
Case “float” ‘decimal types
If iLen <= 24 Then
sNewDataType = “float(0)”
Else
sNewDataType = “float(25)”
End If
Case “decimal”
sNewDataType = “decimal(” & iLen & “,” & iLen2 & “)”
Case “numeric”
sNewDataType = “numeric(” & iLen & “,” & iLen2 & “)”
Case “double”
sNewDataType = “float(25)”
Case “double precision”
sNewDataType = “float(53)”
Case “date”, “time”, “year” ‘datetime types
sNewDataType = “smalldatetime”
Case Else
sOutput = sOutput & “Unknown datatype = ‘” & sDatatype & “‘”
sMSSQL = “”
Exit For
End Select
sMSSQL = sMSSQL & Regex.Replace(sVarName, “`([^`]*)`”, “[$1]“) & ” ” & sNewDataType & “,” & vbCrLf
End If
Next
If sOutput = “” Then
sOutput = “Conversion Complete”
End If
ConvertTable = sOutput
End Function
End Class
Thank you for your code, it provided a nice starting point for the changes I added!
~ Kurt
Kurt May 29
Whoops, lost the html. That first method should read
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Dim sMySQL As String = File.ReadAllText(Server.MapPath(”~/temp/mysql-script.txt”))
Dim sMSSQL As String = String.Empty
Dim output As String = String.Empty
output += ConvertTable(sMySQL, sMSSQL)
Response.Write(”<h2>Conversion Results</h2><textarea rows=5 cols=100>”)
Response.Write(output)
Response.Write(”</textarea><h2>MS SQL Code</h2><textarea rows=50 cols=100>”)
Response.Write(sMSSQL)
Response.Write(”</textarea>”)
End Sub
~ Kurt
Gath May 29
Hi Kurt - Thanks for posting your code, thats some handy stuff.
Cheers,
Gath
Kurt Jun 18
Here’s the final version. Now processes primary keys, fulltext indexes, default values, and auto_increment. Could definitely use some re-factoring, but I don’t have time for that
Imports System.IO
Imports System.Text.RegularExpressions
Partial Class temp_Default
Inherits System.Web.UI.Page
”’ <summary>
”’ Takes a mysql database definition file made up of insert table statements, and
”’ converts it to a sql database database schema by mapping the data types, primary
”’ keys, and fulltext indexes. Could be improved for more advanced
”’ functionality. Based on the data type mapping given by the MSDN library.
”’ </summary>
”’ <param name=”sender”></param>
”’ <param name=”e”></param>
”’ <remarks>The mysql script must be in a very specific format in order for the code
”’ to work properly. It should be the way that mysql auto-generates create table
”’ statements. This code is a modified version of a script provided by Gath Adams @
”’ http://gathadams.com/2008/02/07/convert-mysql-to-ms-sql-server/ .</remarks>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
‘ Read in the appropriate mysql script file
Dim sMySQL As String = File.ReadAllText(Server.MapPath(”~/temp/mysql-script.txt”))
Dim sMSSQL As String = String.Empty
Dim output As String = String.Empty
‘ Process the mysql script and calculate how long it took
Dim dt As DateTime = DateTime.Now
output += ConvertTable(sMySQL, sMSSQL)
Dim dt2 As DateTime = DateTime.Now
output += vbCrLf + “Processing Time: ” + DateDiff(DateInterval.Second, dt, dt2).ToString + ” sec.”
‘ Print the results for the user to process in textarea’s for
‘ convenient copy-paste
Response.Write(”<h2>Conversion Results</h2><textarea rows=5 cols=100>”)
Response.Write(output)
Response.Write(”</textarea><h2>MS SQL Code</h2><textarea rows=50 cols=100>”)
Response.Write(sMSSQL)
Response.Write(”</textarea>”)
End Sub
”’ <summary>
”’ Converts a mysql script of create table statements to the analogous sql server
”’ statements to create the tables, converting data types, along with primary keys,
”’ fulltext indices, and default values.
”’ </summary>
”’ <param name=”sMySQL”></param>
”’ <param name=”sMSSQL”></param>
”’ <returns></returns>
”’ <remarks></remarks>
Private Function ConvertTable(ByVal sMySQL As String, _
ByRef sMSSQL As String) As String
‘ Setup variables for the code
Dim sLine As String = String.Empty
Dim iC As Integer
Dim sColumnParts() As String
Dim iColumnPartIndex As Integer
Dim sVarName As String = String.Empty
Dim sDatatype As String = String.Empty
Dim sNullable As String = String.Empty
Dim sDefault As String = String.Empty
Dim sLen As String = String.Empty
Dim iLen As Integer
Dim autoIncrement As Boolean = False
Dim sNewDataType As String = String.Empty
Dim sOutput As String = String.Empty
Dim iLen2 As Integer
Dim sLines() As String = sMySQL.Split(vbLf)
‘ Setup variables and some User-defined variables
Dim oldSchema As String = “dbo” ‘ The name of the schema in the source database (default ‘dbo’)
Dim newSchema As String = “dbo” ‘ The name of the schema in the target database (default ‘dbo’)
Dim tableName As String = String.Empty
Dim scriptDropTable As Boolean = True ‘ Whether or not to script dropping the tables in the target db
Dim fullTextEnabled As Boolean = True ‘ Whether or not to generate full-text index code
Dim fullTextCatalog As String = “FullTextCatalog” ‘ The name of the full-text catalog in sql server
Dim primaryKeyName As String = String.Empty
Dim sConstraints As String = String.Empty
‘ Loop through all of the lines in the mysql script code
For iC = 0 To sLines.GetUpperBound(0)
sLine = sLines(iC)
sLine = Replace(Replace(Trim$(sLine), vbCr, “”), vbLf, “”)
‘ Skip the blank lines
If String.IsNullOrEmpty(sLine) Then
‘ do nothing
ElseIf sLine.StartsWith(”CREATE TABLE”) = True Then
‘ Parse the table name out of the first line of the CREATE TABLE statement
tableName = Mid(sLine, sLine.IndexOf(”.”) + 3)
tableName = Trim(Left(tableName, tableName.LastIndexOf(”`”)))
‘ Drop table if it exists for convenient re-run and replacement
If scriptDropTable Then
sMSSQL = sMSSQL & _
“IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[" & newSchema & "].[" & tableName & "]‘) AND type in (N’U')) ” & vbCrLf & _
“DROP TABLE [" & newSchema & "].[" & tableName & "] ” & vbCrLf & _
“GO ” & vbCrLf
End If
‘ Convert CREATE TABLE initial statement from MySql to Sql Server
sMSSQL = sMSSQL & Regex.Replace(sLine, “`([^`]*)`”, “[$1]“).Replace(oldSchema, newSchema) & vbCrLf
ElseIf sLine.StartsWith(”PRIMARY KEY”) Then
‘ Convert primary key designation from MySql to Sql Server
‘ Parse the primary key field name from the mysql line of code
Dim primaryKeyField As String = String.Empty
primaryKeyField = Mid(sLine, sLine.IndexOf(”(”) + 3)
primaryKeyField = Trim(Left(primaryKeyField, primaryKeyField.IndexOf(”)”) - 1))
‘ Generate a sql server alter table statement to add the primary key
sConstraints = sConstraints & _
“ALTER TABLE [" + newSchema + "].[" + tableName + "] ADD CONSTRAINT ” & vbCrLf & _
” [PK_" + Replace(tableName, " ", "_") + "] PRIMARY KEY CLUSTERED ” & vbCrLf & _
” ( ” & vbCrLf & _
” [" + primaryKeyField + "] ” & vbCrLf & _
” ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary]” & vbCrLf
primaryKeyName = “PK_” + Replace(tableName, ” “, “_”)
ElseIf sLine.StartsWith(”FULLTEXT KEY”) Then
‘ fulltext key can only be created in Sql Server if there is a valid
‘ primary key to base it on — apparently this is not necessary in MySql
If fullTextEnabled And Not String.IsNullOrEmpty(primaryKeyName) Then
Dim fullTextName As String = String.Empty
Dim fullTextField As String = String.Empty
‘ Parse the full text catalog name from the code
fullTextName = Mid(sLine, sLine.IndexOf(”`”) + 2)
fullTextName = Left(fullTextName, fullTextName.IndexOf(”`”))
‘ Parse the full text field name from the code
fullTextField = Mid(sLine, sLine.IndexOf(”(”) + 3)
fullTextField = Trim(Left(fullTextField, fullTextField.IndexOf(”)”) - 1))
‘ Generate sql server fulltext index code
sConstraints = sConstraints & _
“CREATE FULLTEXT INDEX ON [" & newSchema & "].[" & tableName & "]” & vbCrLf & _
“( ” & vbCrLf & _
” [" & fullTextField & "] LANGUAGE 0 ” & vbCrLf & _
” ) ” & vbCrLf & _
“KEY INDEX [" & primaryKeyName & "] ” & vbCrLf & _
“ON [" & fullTextCatalog & "] ” & vbCrLf & _
” WITH CHANGE_TRACKING AUTO ” & vbCrLf & _
“GO ” & vbCrLf & _
“ALTER FULLTEXT INDEX ON [" & newSchema & "].[" & tableName & "]” & vbCrLf & _
“ENABLE ” & vbCrLf & _
“GO ” & vbCrLf
End If
ElseIf sLine.StartsWith(”)”) = True Then
‘ Code to finish the alter table statement when we have reached the
‘ last line with the closing parenthesis
sMSSQL = Strings.Left$(sMSSQL, Len(sMSSQL) - 3)
sMSSQL = sMSSQL & vbCrLf & “)” & vbCrLf & vbCrLf
sMSSQL = sMSSQL & sConstraints & vbCrLf & vbCrLf
sConstraints = String.Empty
primaryKeyName = String.Empty
tableName = String.Empty
‘Exit For
Else
‘ Split the parts of the column definition for parsing
sColumnParts = sLine.Replace(”,”, “”).Split(” “)
sVarName = sColumnParts(0)
sVarName = Replace(sVarName, “`”, “”)
sDatatype = sColumnParts(1)
iColumnPartIndex = 2
‘ Parse the data type length
If InStr(sDatatype, “(”) > 0 Then
sLen = Mid$(sDatatype, InStr(sDatatype, “(”))
sLen = sLen.Replace(”(”, “”).Replace(”)”, “”)
If InStr(sLen, “,”) > 0 Then
iLen = Val(sLen.Split(”,”)(0))
iLen2 = Val(sLen.Split(”,”)(1))
Else
iLen = Val(sLen)
End If
sDatatype = Strings.Left$(sDatatype, InStr(sDatatype, “(”) - 1)
End If
autoIncrement = False
sNullable = ” NULL ”
sDefault = String.Empty
‘ process the attributes of the column
While iColumnPartIndex <= UBound(sColumnParts)
‘ Parse the nullability of the field
If sColumnParts(iColumnPartIndex) = “NOT” Then
sNullable = “NOT NULL”
iColumnPartIndex = iColumnPartIndex + 2
ElseIf sColumnParts(iColumnPartIndex) = “NULL” Then
sNullable = “NULL”
iColumnPartIndex = iColumnPartIndex + 1
ElseIf sColumnParts(iColumnPartIndex) = “auto_increment” Then
‘ the mysql field was auto_increment and should be identity in sql server
autoIncrement = True
iColumnPartIndex = iColumnPartIndex + 1
ElseIf sColumnParts(iColumnPartIndex) = “default” Then
‘ parse the default value designation of the column
iColumnPartIndex = iColumnPartIndex + 1
While iColumnPartIndex <= UBound(sColumnParts)
sDefault = sDefault + sColumnParts(iColumnPartIndex) + ” ”
iColumnPartIndex = iColumnPartIndex + 1
End While
Else
iColumnPartIndex = iColumnPartIndex + 1
End If
End While
‘ Add default constraint if the field has a default value
If Not String.IsNullOrEmpty(sDefault) Then
sConstraints = sConstraints & _
“ALTER TABLE [" & newSchema & "].[" & tableName & "] ADD CONSTRAINT ” & vbCrLf & _
” DF_” & Replace(tableName, ” “, “_”) & “_” & Replace(sVarName, ” “, “_”) & _
” DEFAULT ” & sDefault & ” FOR [" & sVarName & "] ” & vbCrLf & _
“GO ” & vbCrLf
End If
If InStr(sDatatype, “,”) > 0 Then
sDatatype = sDatatype.Split(”,”)(0)
End If
‘ Convert the DataType from MySql to Sql Server
Select Case LCase(sDatatype)
Case “bigint”, “datetime”, “timestamp”, “text”, “real” ‘ types that are the same
sNewDataType = sDatatype
Case “tinyint” ‘ integer types
sNewDataType = “tinyint”
’sNewDataType = “smallint” signed tinyint’s should go to smallint instead
Case “smallint”
sNewDataType = “smallint”
Case “int”, “mediumint”, “integer”
sNewDataType = “int”
Case “tinytext” ’string types
sNewDataType = “varchar(256)”
Case “text”, “mediumtext”, “longtext”
sNewDataType = “text”
Case “varchar”
sNewDataType = “varchar(” & iLen & “)”
Case “char”
sNewDataType = “char(” & iLen & “)”
Case “tinyblob” ‘blob types
sNewDataType = “binary(256)”
Case “blob”
sNewDataType = “varbinary(max)”
Case “mediumblob”, “longblob”
sNewDataType = “image”
Case “float” ‘decimal types
If iLen <= 24 Then
sNewDataType = “float(0)”
Else
sNewDataType = “float(25)”
End If
Case “decimal”
sNewDataType = “decimal(” & iLen & “,” & iLen2 & “)”
Case “numeric”
sNewDataType = “numeric(” & iLen & “,” & iLen2 & “)”
Case “double”
sNewDataType = “float(25)”
Case “double precision”
sNewDataType = “float(53)”
Case “date”, “time”, “year” ‘datetime types
sNewDataType = “smalldatetime”
Case Else
sOutput = sOutput & “Unknown datatype = ‘” & sDatatype & “‘”
End Select
‘ Add the code for the column to the sql server statement
sMSSQL = sMSSQL & vbTab & “[" & sVarName & "] ” & sNewDataType & ” ” & sNullable
If autoIncrement Then
sMSSQL = sMSSQL & ” IDENTITY (1, 1)”
End If
sMSSQL = sMSSQL & “,” & vbCrLf
End If
Next
If sOutput = “” Then
sOutput = “Conversion Complete”
End If
ConvertTable = sOutput
End Function
End Class