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

score 3 · Answer 1 · answered Feb 26 '14 at 11:49

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.

Ahh This works but it is grabbing the names from the destination table instead of the source table which is the dataTable. This is what is different from my question than the other ones I have found in StackOverflow. — JPK, Feb 26 '14 at 11:59
I am not sure I understand the problem? You can get the columns in the source table using `dataTable.Columns`, it does not matter if compare Source to Destination, or Destination to Source, the list of matched columns will still be the same. — GarethD, Feb 26 '14 at 12:12
OK Let me clarify. I can get the columns, getting them is not a problem at all. My hope was to not need to get them. I want to copy the dataTable as is into the SQL table. The c# equivalent to something like: SELECT * into dbo.test (destination table) FROM dataTable (c# dataTable source table) — JPK, Feb 26 '14 at 12:55
Are you able to use SSIS? It sounds like the [Transfer SQL Server Objects Task](http://technet.microsoft.com/en-us/library/ms142159.aspx) is very close to what you need. Although it is not generic, you have to specify your tables. Alternatively you may be able to implement something using [the underlying DDL](http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.transfersqlserverobjectstask.transfersqlserverobjectstask.aspx), but I have not really explored this option. — GarethD, Feb 26 '14 at 13:30

Alberto Solano · Accepted Answer · 2014-02-26T16:06:40.193

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.

score 0 · Answer 3 · answered Sep 28 '18 at 07:42

0

You can add

   sbc.ColumnMappings.Add(0, 0);
   sbc.ColumnMappings.Add(1, 1);
   sbc.ColumnMappings.Add(2, 2);
   sbc.ColumnMappings.Add(3, 3);
   sbc.ColumnMappings.Add(4, 4);

before executing

   sbc.WriteToServer(dataTable);

Thank you !!

answered Sep 28 '18 at 07:42

KaNcHaN WaKoDiKaR

1
6

SqlBulkCopy ColumnMapping Error

3 Answers3