SqlBulkCopy ColumnMapping Error

Question

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);
    }
}

Solution 1

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.

Solution 2

I would add some validation to this to check what columns your source and destination tables have in common.

This essentially queries the system views (I have assumed SQL Server but this will be easily adaptable for other DBMS), to get the column names in the destination table (excluding identity columns), iterates over these and if there is a match in the source table adds the column mapping.

    public void BatchBulkCopy(string connectionString, DataTable dataTable, string DestinationTbl, int batchSize)
    {
        using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
        {
            sbc.DestinationTableName = DestinationTbl;

            string sql = "SELECT name FROM sys.columns WHERE is_identity = 0 AND object_id = OBJECT_ID(@table)";
            using (var connection = new SqlConnection(connectionString))
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@table", DestinationTbl);
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var column = reader.GetString(0);
                        if (dataTable.Columns.Contains(column))
                        {
                            sbc.ColumnMappings.Add(column, column);
                        }
                    }
                }
            }
            // Number of records to be processed in one go
            sbc.BatchSize = batchSize;

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

This could still get invalid cast errors as there is no data type check, but should get you started for a generic method.