Gath Adams

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

Name (required)

Mail (will not be published) (required)

Website