SQL Bulk Copy not working with sparse column

Question

SQL Bulk Copy not working with sparse column

c# sqlbulkcopy sql-server sql-server-2008 sql-server-2008-r2

Question

I am using C#'s SQLBulkCopy class to insert multiple rows into SQL Server 2008 R2.. It is working for normal table.. but not working for the table which has sparse column.

Do I need to set any extra settings for sparse column in SQLBulkCopy?

My code:

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.KeepIdentity, null))
    {         
       foreach (DataColumn item in objectData.Columns)
              {
                  sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
              }

          sqlBulkCopy.DestinationTableName = "[" + tableName + "]";
          sqlBulkCopy.WriteToServer(objectData);
     }

I am getting this error:

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

kombsh

View more on Stack Overflow

Roman Kolvashenko · Accepted Answer · 8/18/2016 9:02:44 AM

I assume, it is not because of the sparse columns. Do you have any column sets in your table?

If a table has a column_set, sparse columns are not handled as distinct columns. The values of all sparse columns are included in the value of the column_set, which is exported in the same way as an XML column; that is, as varbinary(max) if bound as a binary type, or as nvarchar(max) if bound as a char or wchar type). On import, the column_set value must conform to the schema of the column_set.

You can find more details here Sparse Columns Support in SQL Server Native Client

SQL Bulk Copy not working with sparse column

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator