Sqlbulkcopy使用的几点心得

SQL SERVER在建表的时候，如果列名中带有空格，则会使用中括号将列明包裹起来作为列名，如使用HELLO WORLD作为列名，那么在sqlserver中的列名就是[hello world]，同样，如果列名后面带空格，如name ，那么在sqlserver中的列名就是[name ]。
之所以说这么多，是为了说明：在建立批插时的列对应关系时，要注意目的表中的列名是否有空格，如果有空格，则需要使用中括号来包裹列名。尤其要注意是否有在建表时不注意引入的空格。

同时，在批量插入时，除了建立列对应关系，也可以保持插入部分和表中的部分列完全一致。比如，一个表有10列，需要插入5列数据，则需要保证源表的五列数据顺序以及列名要和目的表完全一样。换句话说，不必保证源表和目的表列数一致，但要保证从首列开始，插入部分的列名和顺序一致。所以，如果目的表带有自增长主键在首列，而源表不带有该列，则在批量插入的时候会报错。这同样适用于带有默认值的列。如目的表首列是带有默认值getdate()的inserttime,而源表没有，则在插入时会报错。
如果不想手动建立批量插入列对应关系，则要谨记这句话：
不必保证源表和目的表列数一致，但要保证从首列开始，插入部分的列名和顺序一致。
否则需要手动建立批量插入列对应关系。

以下是通过数据库获得表的列名，建立对应关系。

DataTable table = new DataTable();
using (var adapter = new SqlDataAdapter(sourceCommand))
{
    adapter.Fill(table);
}

using (SqlBulkCopy bulk = new SqlBulkCopy(targetConnection, SqlBulkCopyOptions.KeepIdentity, null) { DestinationTableName = tableName })
{
    foreach (string columnName in GetMapping(stringSource, stringTarget, tableName))
    {
        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnName, columnName));
    }

    targetConnection.Open();
    bulk.WriteToServer(table);
}

private static IEnumerable<string> GetMapping(string stringSource, string stringTarget, string tableName)
{
    return Enumerable.Intersect(
        GetSchema(stringSource, tableName),
        GetSchema(stringTarget, tableName),
        StringComparer.Ordinal); // or StringComparer.OrdinalIgnoreCase
}

private static IEnumerable<string> GetSchema(string connectionString, string tableName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "sp_Columns";
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@table_name", SqlDbType.NVarChar, 384).Value = tableName;

        connection.Open();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return (string)reader["column_name"];
            }
        }
    }
}

可以通过上述方法给源表列重命名并进行批量插入

    var columnsName = new List<string>(GetSchema(Utilities.m_ConnStr, "destinationtable"));
for (int i = 0; i < dt.Columns.Count; i++)
{
    dt.Columns[i].ColumnName = columnsName[i];
}
try
{
    using (SqlBulkCopy bc = new SqlBulkCopy(Utilities.m_ConnStr))
    {
        bc.BulkCopyTimeout = 10 * 60;
        bc.BatchSize = 10000;
        bc.DestinationTableName = "destinationtable";
        bc.WriteToServer(dt);
    }
}
catch (Exception ex)
{
    Utilities.SendLog(ex.ToString());
}