SqlBulkCopy fails on columns with spaces in them

Question

I know that I need to wrap any column names with spaces in square brackets []. I do this when I'm building my DataTable columns.

for (int i = 0; i < columns.Count; i++)
{
   string colname = columns[i].ToString();

   if (colname.Contains(" "))
   {
      colname = "[" + colname + "]";
   }

   dt.Columns.Add(colname);
}

Then, once I've added all of the data as rows in my DataTable, I call an insert function, which uses the below code to map the columns. The columns names are identical in both the DataTable and the database table.

using (var connection = new SqlConnection(ConnectionString))
{
    SqlTransaction transaction = null;
    connection.Open();

    try
    {
        transaction = connection.BeginTransaction();

        using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
        {
            sqlBulkCopy.DestinationTableName = "DBTableName";

            foreach (DataColumn col in dataTable.Columns)
            {
                sqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }

            sqlBulkCopy.WriteToServer(dataTable);
        }

        transaction.Commit();
    }
    catch (Exception)
    {
        transaction.Rollback();
    }
}

If I set a breakpoint when it is doing the ColumnMapping, the col.ColumName correctly contains the square brackets around the names with spaces in them.

However when it tries to call the WriteToServer method, it fails and the exception says

The given ColumnName 'Name With Space' does not match up with any column in data source.

Why/where is it stripping out the square brackets, and how do I fix this issue?

I may be wrong, but I think the value of brackets is to guard against collision with reserved names. For names with spaces, I wonder if encasing them in quotes (either single or double) would work? — B. Clay Shannon-B. Crow Raven, Dec 08 '15 at 19:13
Try removing the square brackets when adding the ColumnMappings. — cbr, Dec 08 '15 at 19:18
Yeah, not adding the brackets seems to have fixed it. I thought SQL always needed them to deal with the spaces. I had searched and found the below post that confirmed that for me before trying to write my code. Is this just something that newer systems automatically handle correctly? http://stackoverflow.com/questions/23335920/how-to-add-column-mapping-in-sqlbulkcopy-in-c-sharp-where-column-names-contains — wham12, Dec 08 '15 at 20:04

score 2 · Accepted Answer · answered Dec 11 '15 at 19:50

2

Not sure if this has to do with the version of .net or sql server I'm hitting, but I was wrong and didn't need to be adding the square brackets around my column names. It handles the spaces in them correctly anyway.

answered Dec 11 '15 at 19:50

wham12

295
5
21

score 0 · Answer 2 · answered Dec 08 '15 at 21:01

0

If you want to insert all columns then you don't need to have mapping. You can remove this:

    foreach (DataColumn col in dataTable.Columns)
    {
            sqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    }

answered Dec 08 '15 at 21:01

j.v.

977
6
15

I am not inserting all columns that are in the database table (nor do I have them in the same order). – wham12 Dec 08 '15 at 21:28
1

Ah, I can't tell you what's the problem but I can give you workaround. Create one temp or lets say staging table that will have exactly the same structure as your data table. And then do bulk insert there, and after that call stored procedure that will move data from that temp (staging) table to original table. – j.v. Dec 08 '15 at 21:51

SqlBulkCopy fails on columns with spaces in them

2 Answers2