BulkCopy WriteToServer() Inserts Into Wrong Column

Question

I am trying to write a DataTable into my destination table on Sql Server. DataTable dt only contains one column and its called Email. My destination table contains tpid, lastname, age, date and email. This is the code I am using for bulk copy:

using (SqlConnection sqlConn = new SqlConnection(sqlCs))
{
    sqlConn.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
    {
        bulkCopy.DestinationTableName = destinationTableName;
        bulkCopy.WriteToServer(dt);
    }
}

What I see is that in my destination table, I get the value of Email from my DataTable being inserted into the tpid column instead of email. I am using no column mapping here, but shouldn't bulk copy match Email with email automatically?

Answer 1

Consider trying this mapping:

SqlBulkCopyColumnMapping mapMAIL = new SqlBulkCopyColumnMapping("Email", "email");
bulkCopy.ColumnMappings.Add(mapMAIL);

You can query the MySQL schema for the source and destination tables and generate automappings based on field names, if you want.