SqlBulkCopy calculated field

I am working on moving a database from MS Access to sql server. To move the data into the new tables I have decided to write a sync routine as the schema has changed quite significantly and it lets me run testing on programs that run off it and resync whenever I need new test data. Then eventually I will do one last sync and start live on the new sql server version.

Unfortunately I have hit a snag, my method is below for copying from Access to SQLServer

public static void BulkCopyAccessToSQLServer
        (string sql, CommandType commandType, DBConnection sqlServerConnection,
            strin开发者_运维技巧g destinationTable, DBConnection accessConnection, int timeout)
    {
        using (DataTable dt = new DataTable())
        using (OleDbConnection conn = new OleDbConnection(GetConnection(accessConnection)))
        using (OleDbCommand cmd = new OleDbCommand(sql, conn))
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
        {
            cmd.CommandType = commandType;
            cmd.Connection.Open();
            adapter.SelectCommand.CommandTimeout = timeout;
            adapter.Fill(dt);

            using (SqlConnection conn2 = new SqlConnection(GetConnection(sqlServerConnection)))
            using (SqlBulkCopy copy = new SqlBulkCopy(conn2))
            {
                conn2.Open();

                copy.DestinationTableName = destinationTable;
                copy.BatchSize = 1000;
                copy.BulkCopyTimeout = timeout;
                copy.WriteToServer(dt);
                copy.NotifyAfter = 1000;
            }
        }
    }

Basically this queries access for the data using the input sql string this has all the correct field names so I don't need to set columnmappings.

This was working until I reached a table with a calculated field. SQLBulkCopy doesn't seem to know to skip the field and tries to update the column which fails with error "The column 'columnName' cannot be modified because it is either a computed column or is the result of a union operator."

Is there an easy way to make it skip the calculated field?

I am hoping not to have to specify a full column mapping.

There are two ways to dodge this:

• use the ColumnMappings to formally define the column relationship (you note you don't want this)
• push the data into a staging table - a basic table, not part of your core transactional tables, whose entire purpose is to look exactly like this data import; then use a TSQL command to transfer the data from the staging table to the real table

I always favor the second option, for various reasons:

• I never have to mess with mappings - this is actually important to me ;p
• the insert to the real table will be fully logged (SqlBulkCopy is not necessarily logged)
• I have the fastest possible insert - no constraint checking, no indexing, etc
• I don't tie up a transactional table during the import, and there is no risk of non-repeatable queries running against a partially imported table
• I have a safe abort option if the import fails half way through, without having to use transactions (nothing has touched the transactional system at this point)
• it allows some level of data-processing when pushing it into the real tables, without the need to either buffer everything in a DataTable at the app tier, or implement a custom IDataReader