SQL code to insert multiple rows in ms-access table

Question

I'm trying to speed up my code and the bottleneck seems to be the individual insert statements to a Jet MDB from outside Access via ODBC. I need to insert 100 rows at a time and have to repeat that many times.

It is possible to insert multiple rows in a table with SQL code? Here is some stuff that I tried but neither of them worked. Any suggestions?

INSERT INTO tblSimulation (p, cfYear, cfLocation, Delta, Design, SigmaLoc,
                                Sigma, SampleSize, Intercept) VALUES 
(0, 2, 8.3, 0, 1, 0.5, 0.2, 220, 3.4),
(0, 2.4, 7.8, 0, 1, 0.5, 0.2, 220, 3.4), 
(0, 2.3, 5.9, 0, 1, 0.5, 0.2, 220, 3.4)


INSERT INTO tblSimulation (p, cfYear, cfLocation, Delta, Design, SigmaLoc,
                                Sigma, SampleSize, Intercept) VALUES 
(0, 2, 8.3, 0, 1, 0.5, 0.2, 220, 3.4) UNION
(0, 2.4, 7.8, 0, 1, 0.5, 0.2, 220, 3.4) UNION 
(0, 2.3, 5.9, 0, 1, 0.5, 0.2, 220, 3.4)

Answer 1

Your query should look like this

insert into aaa (col1, col2)
select * from (select 'yourdatarow1col1' as col1 , 'yourdatarow1col2' as col2 from ddd
union all
select 'yourdatarow2col1' as col1, 'yourdatarow1col2' as col2 from ddd) as tmp

aaa: Is your target table in access

ddd: create a table in access db with one colum and must have 1 row, doesn't not mater what data.

aliases: All aliases should have same name and order has target table

Hope works for you, my process drop from 15 minutes to 1.30 minutes

Bye Gus

Answer 2

Not that I am aware of. And I have felt this pain before. The good news is that if you wrap the insert in a transaction and don't close the connection between each call (eg--pass the command as a parameter) then it is orders of magnitude quicker than open()->INSERT->close() is.

Use a code pattern something like:

using (DbConnection conn = new OdbcConnection())
using (DbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "INSERT INTO foo(bar) VALUES (@bar)";
    DbParameter p = cmd.CreateParameter();
    p.ParameterName = "@bar";
    cmd.CommandType = CommandType.Text;
    conn.Open();
    using (DbTransaction tran = conn.BeginTransaction())
    {
        cmd.Transaction = tran;
        try
        {
            for (int i = 0; i < 1000; i++)
            {
                p.Value = i;
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
            conn.Close();
        }
        catch (Exception)
        {
            tran.Rollback();
            throw;
        }
    }
}

Answer 3

I found an elegant solution within R, (the software I'm working with). The RODBC package has a function sqlSave which allow to append and entire data.frame at once to a table. This works almost twice as fast as individual inserts within a transaction.

library(RODBC)
MDB <- odbcConnectAccess("database.mdb")
sqlSave(channel = MDB, dat = sims, tablename = "tblSimulation", append = TRUE, rownames = FALSE)
odbcClose(MDB)