Skip some columns in SqlBulkCopy

Question

I'm using SqlBulkCopy against two SQL Server 2008 with different sets of columns (going to move some data from prod server to dev). So want to skip some columns not yet existed / not yet removed.

How can I do that? Some trick with ColumnMappings?

Edit:

I do next:

DataTable table = new DataTable();
using (var adapter = new SqlDataAdapter(sourceCommand))
{
 adapter.Fill(table);
}
table.Columns
 .OfType<DataColumn>()
 .ForEach(c => bulk.ColumnMappings.Add(
 new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)));
bulk.WriteToServer(table)

and get:

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

Answer 1

DataTable table = new DataTable();
using (var adapter = new SqlDataAdapter(sourceCommand))
{
 adapter.Fill(table);
}
using (SqlBulkCopy bulk = new SqlBulkCopy(targetConnection, SqlBulkCopyOptions.KeepIdentity, null) { DestinationTableName = tableName })
{
 foreach (string columnName in GetMapping(stringSource, stringTarget, tableName))
 {
 bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnName, columnName));
 }
 targetConnection.Open();
 bulk.WriteToServer(table);
}
private static IEnumerable<string> GetMapping(string stringSource, string stringTarget, string tableName)
{
 return Enumerable.Intersect(
 GetSchema(stringSource, tableName),
 GetSchema(stringTarget, tableName),
 StringComparer.Ordinal); // or StringComparer.OrdinalIgnoreCase
}
private static IEnumerable<string> GetSchema(string connectionString, string tableName)
{
 using (SqlConnection connection = new SqlConnection(connectionString))
 using (SqlCommand command = connection.CreateCommand())
 {
 command.CommandText = "sp_Columns";
 command.CommandType = CommandType.StoredProcedure;
 command.Parameters.Add("@table_name", SqlDbType.NVarChar, 384).Value = tableName;
 connection.Open();
 using (var reader = command.ExecuteReader())
 {
 while (reader.Read())
 {
 yield return (string)reader["column_name"];
 }
 }
 }
}

• Mr.X: @abtishchev - cool and a reusable. I believe 'stringTarget' and 'stringSource' are column names, right?
• abatishchev: @student: hey, IIRC these are connection strings to source and target databases respectively.
• Sonny Childs: Fantastic. I needed to add a little bit of code to properly handle schema and database names, but this is exactly what I needed.
• abatishchev: @Kev: frankly I would suggest two things: either change your IT department, or if you can't - change your job. We spend a good potion of life at work and life is too short to spend a good portion of it with unprofessional/obsolete/stupid coworkers.
• JJS: horray for using sp_columns instead of select * from table where 1=0 and getting the SchemaTable() from the DataReader. You're a hero.

Answer 2

When SqlBulkCopyColumnMapping is used, only columns for which mappings are created will be copied.

If you do not create a mapping for a column, it will be ignored by the copy process.

You can see this in the demo code here - the sample source table in the AdventureWorks demo database contains more columns than are mapped or copied.

EDIT

It's difficult to be certain without more information about the database schema, but at a guess the issue is with this statement:

new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)

From your description, it sounds like not all the columns in the source table exist in the destination table. You need a filter in your SqlBulkCopyColumnMapping construction loop to skip any columns which do not exist in the destination.

My C# is not good enough to give a example which I'm confident will work, but in pseudocode it would be

foreach column c in sourcetable
{
 if c.ColumnName exists in destination_table.columns
 {
 new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)
 }
}

(I'm sure it's possible to convert this to a lambda expression)

Note that this is not particularly robust in the scenario where the column names match but the datatypes are incompatible.

• abatishchev: See my updated post. What am I doing wrong? Probably I understand - source has a column, but target - don't. I should compare source/target schema and use only columns exists in both
• Ed Harper: @abatishchev - added more detail
• abatishchev: Thanks! You cleared my vision. But your example is not suitable unfortunately for me because I have no target table, only its name. So have to call sp_Columns to determine table columns.

Answer 3

Ed Harper, this is what it looks like without pseudo code (in this case from DataTable dt (fully defined) to an existing table in the db:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
 bulkCopy.DestinationTableName = "dbo.DepartmentsItems";
 // Write from the source to the destination.
 foreach (DataColumn c in dt.Columns)
 {
 bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
 }
 bulkCopy.WriteToServer(dt);
 return dt.Rows.Count;
}

Answer 4

try this:SqlBulkCopyColumnMapping Class

Hope you are looking for the same

• abatishchev: Yes, I'm talking about exactly this class. But how to skip a column in source? .Add(new SqlDataMapping("deleted-column-on-target", "")? Of course I can remove it from source in underlying query - SELECT a,b,c instead of SELECT * - but this is not a solution
• cjk: If you don't wnat to copy it from the source to the destination, just leave it out of the mapping. The mapping will only copy data from specified columns.