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

Popular Answer

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



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