SqlBulkCopy ColumnMapping Error

My goal is to copy generic tables from one database to another. I would like to have it copy the data as is and it would be fine to either delete whatever is in the table or to add to it with new columns if there are new columns. The only thing I may want to change is to add something for versioning which can be done in a seperate part of the query.

Opening the data no problem but when I try a bulk copy but it is failing. I have gone though several posts and the closest thing is this one: SqlBulkCopy Insert with Identity Column

I removed the SqlBulkCopyOptions.KeepIdentity from my code but it still is throwing

“The given ColumnMapping does not match up with any column in the source or destination” error

I have tried playing with the SqlBulkCopyOptions but so far no luck.

Ideas?

public void BatchBulkCopy(string connectionString, DataTable dataTable, string DestinationTbl, int batchSize)
{
    // Get the DataTable 
    DataTable dtInsertRows = dataTable;

    using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
    {
        sbc.DestinationTableName = DestinationTbl;

        // Number of records to be processed in one go
        sbc.BatchSize = batchSize;

        // Finally write to server
        sbc.WriteToServer(dtInsertRows);
    }
}

Answer

If I could suggest another approach, I would have a look at the SMO (SQL Server Management Objects) library to perform such tasks. You can find an interesting article here. Using SMO, you can perform tasks in SQL Server, such a bulk copy, treating tables, columns and databases as objects.

Some time ago, I used SMO in a small open source application I developed, named SQLServerDatabaseCopy. To copy the data from table to table, I created this code (the complete code is here):

foreach (Table table in Tables)
    {
        string columnsTable = GetListOfColumnsOfTable(table);

        string bulkCopyStatement = "SELECT {3} FROM [{0}].[{1}].[{2}]";
        bulkCopyStatement = String.Format(bulkCopyStatement, SourceDatabase.Name, table.Schema, table.Name, columnsTable);

        using (SqlCommand selectCommand = new SqlCommand(bulkCopyStatement, connection))
        {
            LogFileManager.WriteToLogFile(bulkCopyStatement);
            SqlDataReader dataReader = selectCommand.ExecuteReader();

            using (SqlConnection destinationDatabaseConnection = new SqlConnection(destDatabaseConnString))
            {
                if (destinationDatabaseConnection.State == System.Data.ConnectionState.Closed)
                {
                    destinationDatabaseConnection.Open();
                }

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationDatabaseConnection))
                {
                    bulkCopy.DestinationTableName = String.Format("[{0}].[{1}]", table.Schema, table.Name);

                    foreach (Column column in table.Columns)
                    {
                        //it's not needed to perfom a mapping for computed columns!
                        if (!column.Computed)
                        {
                            bulkCopy.ColumnMappings.Add(column.Name, column.Name);
                        }
                    }

                    try
                    {
                        bulkCopy.WriteToServer(dataReader);
                        LogFileManager.WriteToLogFile(String.Format("Bulk copy successful for table [{0}].[{1}]", table.Schema, table.Name));
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }
                    finally
                    {
                        //closing reader
                        dataReader.Close();
                    }
                }
            }
        }
    }

As you can see, you have to add the ColumnMappings to the BulkCopy object for each column, because you have to define which column of source table must be mapped to a column of destination table. This is the reason of your error that says: The given ColumnMapping does not match up with any column in the source or destination.