Create New Table on SQL Server with another table's schema

Question

I have a procedure in VB.net that when ran, determines if a table exists or not. If it doesn't exist I want to create a table on SQL Server with the same schema as a local FoxPro table. Is this something that can be done?

Here is what I have so far. Right now it just grabs the Schema from a Visual Foxpro table and displays it. Not sure where to go from here. Any ideas?

Private Sub dwprm01()
    Try
        Dim tableName As String = "dwprm01"
        Dim tableExists As Boolean = False
        Dim foxConn As New OleDbConnection("Provider=vfpoledb.1;Data Source=Z:\update_dwprm01\" & tableName & ".DBF;Collating Sequence=general;")

        sConn.Open()
        Dim restrictions(3) As String
        restrictions(2) = tableName
        Dim dbTbl As DataTable = sConn.GetSchema("Tables", restrictions)

        Console.WriteLine("Checking if " & tableName & " exists")

        If dbTbl.Rows.Count = 0 Then
            'Table does not exist
            tableExists = False

            Console.WriteLine(tableName & " does not exist")
            Console.WriteLine()
            Console.WriteLine("Creating " & tableName)

            Dim fSQL = "SELECT * FROM " & tableName
            Dim cmd As New OleDbCommand(fSQL, foxConn)

            foxConn.Open()

            Dim myReader As OleDbDataReader = cmd.ExecuteReader()
            Dim schema As DataTable = myReader.GetSchemaTable()

            For Each row As DataRow In schema.Rows
                For Each col As DataColumn In schema.Columns
                    Console.WriteLine(col.ColumnName & " = " & row(col).ToString())
                Next
            Next
            myReader.Close()
            foxConn.Close()
        Else
            'Table exists
            tableExists = True
            Console.WriteLine(tableName & " exists")
        End If

        dbTbl.Dispose()
        sConn.Close()
        sConn.Dispose()

    Catch ex As Exception
        Console.WriteLine(ex.ToString())
    End Try
End Sub

Answer 1

I was able to finally figure this out. Here is what I had to do. The commented block will show you different rows in the tables schema. The Case statement is yet to be finished either, but you can add onto this as you run into more datatypes needing to be converted.

Imports System.Data.OleDb

Module prm01_up
    Dim sConn As New OleDbConnection("Provider=SQLNCLI10;Server=;Database=;Uid=;Pwd=;")

Sub Main()
    Dim foxTables() As String = {"dwprm01", "lkpbrnch", "add_me", "empmastr"}

    For Each tableName As String In foxTables
        seekAndCreate(tableName)
    Next

    Console.WriteLine()
    Console.WriteLine("Press any key to continue...")
    Console.ReadKey()
End Sub

Private Sub seekAndCreate(ByRef tableName As String)
    Try
        Dim tableExists As Boolean = False
        Dim foxConn As New OleDbConnection("Provider=vfpoledb.1;Data Source=Z:\update_dwprm01\" & tableName & ".DBF;Collating Sequence=general;")

        sConn.Open()
        Dim restrictions(3) As String
        restrictions(2) = tableName
        Dim dbTbl As DataTable = sConn.GetSchema("Tables", restrictions)

        Console.WriteLine("Checking if " & tableName & " exists")

        If dbTbl.Rows.Count = 0 Then
            'Table does not exist
            tableExists = False

            Console.WriteLine(tableName & " does not exist")
            Console.WriteLine()
            Console.WriteLine("Creating " & tableName)

            Dim foxDs As New DataSet
            Dim fSQL As String = "USE " & tableName
            Dim fCmd As New OleDbCommand(fSQL, foxConn)

            foxConn.Open()

            Dim objDR As OleDbDataReader
            objDR = fCmd.ExecuteReader(CommandBehavior.CloseConnection)

            Dim schemaTable = objDR.GetSchemaTable()
            Dim colName As String = String.Empty
            Dim colSize As String = String.Empty
            Dim colDataType As String = String.Empty
            Dim newDataType As String = String.Empty
            Dim allColumns As String = String.Empty
            Dim colPrecision As String = String.Empty
            Dim colScale As String = String.Empty
            Dim createTable As New OleDbCommand

            'For Each x As DataRow In schemaTable.Rows
            '    For Each y As DataColumn In schemaTable.Columns
            '        Console.WriteLine(y.ColumnName)
            '    Next
            '    Console.WriteLine()
            'Next

            For Each myField As DataRow In schemaTable.Rows
                colName = myField(0).ToString
                colSize = myField(2).ToString
                colDataType = myField(5).ToString
                colPrecision = myField(3).ToString
                colScale = myField(4).ToString

                Select Case colDataType
                    Case "System.String"
                        newDataType = "varchar" & "(" & colSize & "), "
                    Case "System.Decimal"
                        newDataType = "numeric(" & colPrecision & ", " & colScale & "), "
                    Case "System.DateTime"
                        newDataType = "datetime, "
                    Case "System.Int32"
                        newDataType = "int,"
                    Case Else
                        newDataType = colDataType.ToString()
                End Select
                allColumns += "[" & colName & "]" & " " & newDataType
            Next
            Console.WriteLine(allColumns.Substring(0, allColumns.Length - 2))

            createTable.Connection = sConn
            createTable.CommandType = CommandType.Text
            createTable.CommandText = "CREATE TABLE " & tableName & " (" & allColumns & ")"
            createTable.ExecuteNonQuery()
            foxConn.Close()
        Else
            'Table exists
            tableExists = True
            Console.WriteLine(tableName & " exists")
            Console.WriteLine()
        End If

        foxConn.Dispose()
        dbTbl.Dispose()
        sConn.Close()

    Catch ex As Exception
        Console.WriteLine(ex.ToString())
    End Try
End Sub
End Module

Answer 2

Since you've already verified the new table does note exist, you just want to execute a query like this:

 SELECT TOP 0 * INTO NewTable FROM OriginalTable

That will create a new table with no rows whose structure matches your original.