Skip some columns in SqlBulkCopy

Question

I'm using SqlBulkCopy against two SQL Server 2008 with different sets of columns (going to move some data from prod server to dev). So want to skip some columns not yet existed / not yet removed.

How can I do that? Some trick with ColumnMappings?

Edit:

I do next:

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

table.Columns
    .OfType<DataColumn>()
    .ForEach(c => bulk.ColumnMappings.Add(
        new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)));

bulk.WriteToServer(table)

and get:

The given ColumnMapping does not match up with any column in the source or destination.

Answer 1

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"];
            }
        }
    }
}

Answer 2

When SqlBulkCopyColumnMapping is used, only columns for which mappings are created will be copied.

If you do not create a mapping for a column, it will be ignored by the copy process.

You can see this in the demo code here - the sample source table in the AdventureWorks demo database contains more columns than are mapped or copied.

EDIT

It's difficult to be certain without more information about the database schema, but at a guess the issue is with this statement:

new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)

From your description, it sounds like not all the columns in the source table exist in the destination table. You need a filter in your SqlBulkCopyColumnMapping construction loop to skip any columns which do not exist in the destination.

My C# is not good enough to give a example which I'm confident will work, but in pseudocode it would be

foreach column c in sourcetable
{
    if c.ColumnName exists in destination_table.columns
    {
          new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)
    }
}

(I'm sure it's possible to convert this to a lambda expression)

Note that this is not particularly robust in the scenario where the column names match but the datatypes are incompatible.

Answer 3

Ed Harper, this is what it looks like without pseudo code (in this case from DataTable dt (fully defined) to an existing table in the db:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
    bulkCopy.DestinationTableName = "dbo.DepartmentsItems";

    // Write from the source to the destination.
    foreach (DataColumn c in dt.Columns)
    {
        bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
    }

    bulkCopy.WriteToServer(dt);
    return dt.Rows.Count;
}

Answer 4

try this:SqlBulkCopyColumnMapping Class

Hope you are looking for the same