Fastest way to import records from MS Access table into Oracle table using ManagedDataAccess

Question

I need to import a lot of records from a MS Access table into an Oracle table using C# and Oracle.ManagedDataAccess. Assuming the tables already exist, what is the fastest way to copy the records from Access to Oracle? I am now using an OleDbConnection but if there is a faster way using Interop, VBA and sqlloader I am open to that as well.

Here is my code so far. Unfortunately it takes days to complete where it should take minutes.

            private void CopyRecords(DataTable schemaTable, string tableName, OleDbDataReader accessReader, OracleConnection oracleConnection)
            {
                if (!accessReader.HasRows)
                {
                    return;
                }

                var oracleTableName = tableName.ToUpper().Replace(' ', '_');
                var statements = new List<string>();
                var query = "";

                OracleCommand oracleCommand;

                while (accessReader.Read())
                {
                    var valueSpecs = new string[schemaTable.Rows.Count];
                    var fieldSpecs = new string[schemaTable.Rows.Count];
                    for (int i = 0; i < schemaTable.Rows.Count; ++i)
                    {
                        var name = schemaTable.Rows[i].ItemArray[0];
                        var fieldName = name.ToString().ToUpper().Replace(' ', '_');
                        var dataType = schemaTable.Rows[i].ItemArray[5];
                        var value = "";
                        switch (dataType.ToString())
                        {
                            case "System.String":
                                value = $"'{accessReader[i]}'";
                                break;
                            case "System.Int32":
                            case "System.Int16":
                            case "System.Double":
                            case "System.Decimal":
                                value = accessReader[i].ToString();
                                if (value.Length < 1)
                                {
                                    value = "0";
                                }
                                break;
                            default:
                                throw new Exception();
                        }
                        valueSpecs[i] = value;
                        fieldSpecs[i] = fieldName;
                    }

                    var fields = string.Join(",", fieldSpecs);
                    var values = string.Join(",", valueSpecs);
                    var statement = $"INSERT INTO MDB_{oracleTableName} ({fields}) VALUES ({values})";
                    statements.Add(statement);

                    if (statements.Count >= 10000)
                    {
                        query = $"BEGIN\r\n{string.Join(";\r\n", statements)};\r\nEND;";
                        oracleCommand = new OracleCommand(query, oracleConnection);
                        oracleCommand.ExecuteNonQuery();
                    }
                }

                query = $"BEGIN\r\n{string.Join(";\r\n", statements)};\r\nEND;";
                oracleCommand = new OracleCommand(query, oracleConnection);
                oracleCommand.ExecuteNonQuery();
            }

Answer 1

You create an INSERT statement for each single row, that's basically the slowest possible way of doing it.

Have a look at "prepared statements", would be similar to this:

// Prepare the statement
var statement = $"INSERT INTO MDB_{oracleTableName} ({fields}) VALUES (:p0";
for (int i = 1; i < fieldSpecs.Count; ++i) {
    statement = statement  + ",:p1";
}
statement = statement  + ")";
var cmd = new OracleCommand(statement, oracleConnection);

// Add parameters
for (int i = 0; i < fieldSpecs.Count; ++i) {
    cmd.Parameters.Add(String.Format("p{0}", i), OracleDbType.Varchar2); 
    // needs to be more advanced in order to cover also other data types, not just Varchar2
}

// Assign parameter values and execute
while (accessReader.Read()) {
    for (int i = 0; i < fieldSpecs.Count; ++i) {
        cmd.Parameters[i].Value = accessReader[i];
    }
    cmd.ExecuteNonQuery();
}