SqlBulkCopy rows from one table to another but with different columns order

.net sqlbulkcopy sql-server

Question

I have two tables in two different servers. That tables have same columns but in different order and I cant perform bulk copy;

I'm tried to do

        var copier = new SqlBulkCopy(destination_connection);
        copier.DestinationTableName = destination_table_name;

        foreach (DataColumn column in source_table.Columns) {
            var mapping = new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName);
            copier.ColumnMappings.Add(mapping);
        }

        copier.WriteToServer(source_table);

but this not helps me and InvalidOperationException is raised.

p.s. I use .NET 4.0

EDIT 1:

Exception is

Specified ColumnMapping does not match any column in the data source or destination.

but I sure that all columns have the same name and type. Is there any way to find out which one mapping cause to error?

Accepted Answer

Answering the question "Is there any way to find out which one mapping cause to error?"

Yes, use a simple process of elimination. First map only a single column, using hard-coded column names. Get that to work. If you cannot get even a single column to work, then there's something more fundamental wrong. (for instance, your destination table may not be what you think it is).

Once you have it working for a single column, then add more columns, one at a time. When it starts to fail again, that is the column that has the problem.


Popular Answer

This is what I use for SqlBulkCopy in my applications

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringSettings.ConnectionString);
bulkCopy.BulkCopyTimeout = 0;   // Sets the timeout to unlimited

// Iterates through each column in the datatable
foreach (DataColumn column in table.Columns)
{
    // Makes a connection map between the datatable and the database table
    bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}

// Sets the desitination table
bulkCopy.DestinationTableName = table.TableName;
bulkCopy.WriteToServer(table);

I don't create a SqlBulkCopyColumnMapping; instead I use the overloaded method for the column names (although this shouldn't matter). The column names in my table are exactly the same as the column names in my databases.

Check to see if the column names/types in your database are the same as the column names in your datatable. Also make sure there are the same number of columns in both the datatable and the database. If the names of the columns differ, you have to add the column mappings manually like so

bulkCopy.ColumnMappings.Add("dataTableColumn1", "databaseColumn1");
bulkCopy.ColumnMappings.Add("dataTableColumn2", "databaseColumn2");
...


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why