Generate a Microsoft SQL Server Table from an ODBC Data Source table and bulk copy its contents. For my real world use case (servers on the same LAN) I get ~6800 rows / second for a 2 million record dataset when using this class via the Parallel class in

SqlServerTableCreatorFromODBC.cs

using System;
using System.Data.Odbc;
using System.Data.SqlClient;

namespace ODBC_Import
{
    // Sourced from: http://darrylagostinelli.com/2011/06/27/create-a-sql-table-from-a-datatable-in-c-net/
    // with modifications for copying from an ODBC data source
    class SqlServerTableCreator
    {
        public static void CreateAndPopulateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            DropTableIfExists(tableName, sqlcon);
            CreateTable(tableName, sqlcon, reader);
            PopulateTable(tableName, sqlcon, reader);
        }

        public static void PopulateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(
                sqlcon,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.KeepNulls |
                SqlBulkCopyOptions.KeepIdentity,
                null
                );

            bulkCopy.DestinationTableName = tableName;

            // This is roughly optimal it seems: http://sqlblog.com/blogs/linchi_shea/archive/2011/07/01/performance-impact-what-is-the-optimal-payload-for-sqlbulkcopy-writetoserver.aspx
            bulkCopy.BatchSize = 500;

            bulkCopy.WriteToServer(reader);
        }

        public static int CreateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            // Create the table.
            string sql = GenerateTableSQL(tableName, reader);
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            return cmd.ExecuteNonQuery();
        }

        public static string GenerateTableSQL(string tableName, OdbcDataReader reader)
        {
            string sql = "CREATE TABLE [" + tableName + "] (\n";

            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                sql += "\t[" + reader.GetName(i) + "] " + SQLGetType(reader.GetFieldType(i)) + ",\n";
            }

            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n)";

            return sql;
        }

        public static int DropTableIfExists(string tableName, SqlConnection sqlcon)
        {
            // Drop the table if it already exists.
            string sql = String.Format("IF OBJECT_ID('{0}') IS NOT NULL DROP TABLE {0}", tableName);
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            return cmd.ExecuteNonQuery();
        }

        // Return T-SQL data type definition, based on schema definition for a column
        public static string SQLGetType(Type type)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return "VARCHAR(8000)";

                case "System.Decimal":
                case "System.Double":
                case "System.Single":
                    return "REAL";

                case "System.Int64":
                    return "BIGINT";

                case "System.Int16":
                case "System.Int32":
                    return "INT";

                case "System.DateTime":
                    return "DATETIME";

                case "System.Boolean":
                    return "BIT";

                case "System.Byte":
                    return "TINYINT";

                case "System.Guid":
                    return "UNIQUEIDENTIFIER";

                default:
                    throw new Exception(type.ToString() + " not implemented.");
            }
        }
    }
}