SQLBulkCopy: Does Column Count make difference?

sqlbulkcopy sql-server

Question

I try to search but didn't found answer to relative simple thing. I have a CSV, that doesn't have all the column as in my database table, as well as it miss the auto increment, primary key in CSV too.

All I did is I read CSV into the DataSet, and then run a traditional SQLBulkCopy code to read the first table of dataset to database table. But it give me following error:

The given ColumnMapping does not match up with any column in the source or destination.

My code for bulkcopy is

using (SqlBulkCopy blkcopy = new SqlBulkCopy(DBUtility.ConnectionString))
{

    blkcopy.EnableStreaming = true;
    blkcopy.DestinationTableName = "Project_" + this.ProjectID.ToString() + "_Data";
    blkcopy.BatchSize = 100;
    foreach (DataColumn c in ds.Tables[0].Columns)
    {
        blkcopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
    }

    blkcopy.WriteToServer(ds.Tables[0]);
    blkcopy.Close();
}

I add Mapping to test, but it doesn't make difference to remove mapping part. If we remove mapping that it try to match column in order and since column are different in count they end up mismatch datatype and lesser column values etc. Oh yes the column names from CSV does match that from Table, and are in same case.

EDIT: I change the mapping code to compare the column name from live DB. For this I simply run a SQL Select query to fetch 1 record from database table and then do following

foreach (DataColumn c in ds.Tables[0].Columns)
{
    if (LiveDT.Columns.Contains(c.ColumnName))
    {
        blkcopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
    }
    else
    {
        log.WriteLine(c.ColumnName + " doesn't exists in final table");
    }
}

Accepted Answer

I would dump the results of CSV into a staging SQL table...and then do a simple insert from staging table to production table.

also do a simple Import of CSV into SQL Table, maybe there are some empty/invalid columns within CSV file.


Popular Answer

I once had this problem and the cause was a difference in the case of the column names. One of the columns was "Id", but in the DB it was "id".



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