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

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?

Are you sure that 1) All of the source columns exist in the destination, 2) They are all spelled the same, and 3) they all have the same datatype? Any of these could cause that error. — RBarryYoung, Jul 23 '14 at 14:16
Not sure why the downvote, seems like a perfectly legitimate question to me. — RBarryYoung, Jul 23 '14 at 14:17
Downvoted because exception details are missing and the OP doesn't seem to have done anything to resolve the issue or help diagnose it. The missing details are also reason for my close vote. — usr, Jul 23 '14 at 14:21
@usr If you tell the OP what else you expect, they may be able to add it. — RBarryYoung, Jul 23 '14 at 14:25
@usr, I updated question with error details. I don't think you need whole stack — atomAltera, Jul 23 '14 at 14:26
have you checked BulKCopy can deal with this scenario, as it isn't general purpose? As a test try creating the same in SSIS, that would prove that the approach will work. — cjb110, Jul 23 '14 at 14:29
@cjb110 AFAIK, the only things "not General Purpose" about SQLBulkCopy is that it can lock out the table for an extended period, may require privileges (not sure), and takes more programming than a simple insert. It should work on any table. — RBarryYoung, Jul 23 '14 at 14:39

score 3 · Accepted Answer · answered Jul 23 '14 at 14:30

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.

Hm. You right. One column name was misspelled. And by explicit elimination I managed it — atomAltera, Jul 23 '14 at 14:42

TFischer · Answer 2 · 2014-07-23T14:39:18.917

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");
...

It's not clear how this is different from what the OP is doing. Could you point out what is different from their code (or your own original code) that would fix the error? I.E., what's the mistake that is causing the error? — RBarryYoung, Jul 23 '14 at 14:32
I started writing before his edit. I updated my answer with a bit more of an explaination — TFischer, Jul 23 '14 at 14:40

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

2 Answers2