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.