Deprecated: Assigning the return value of new by reference is deprecated in /home/gathadam/public_html/wp-settings.php on line 520

Deprecated: Assigning the return value of new by reference is deprecated in /home/gathadam/public_html/wp-settings.php on line 535

Deprecated: Assigning the return value of new by reference is deprecated in /home/gathadam/public_html/wp-settings.php on line 542

Deprecated: Assigning the return value of new by reference is deprecated in /home/gathadam/public_html/wp-settings.php on line 578

Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/gathadam/public_html/wp-settings.php on line 18
Convert MySQL to MS Sql Server
Previous     Next

Convert MySQL to MS Sql Server

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


If you liked this, then subscribe to my RSS feed

17 comments

  1. 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.

  2. 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

  3. 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. :)

  4. 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.

  5. 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

  6. Tim Mar 11

    Sweet little app! Very useful.

  7. 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.

  8. 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.

  9. 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

  10. 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

  11. 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

  12. Gath May 29

    Hi Kurt - Thanks for posting your code, thats some handy stuff.

    Cheers,
    Gath

  13. 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

  1. Daily Find #29 | TechToolBlog
  2. Convert MySQL to MS SQL Server Part 2
  3. Quick SQL Server Tuning Tip
  4. Pretvorba PHP MySQL aplikacije v PHP MS SQL aplikacijo (apache, php, mdb2, mysql => iis, php, mdb2, mssql) | .: TRSplet - internetne storitve :.