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