在ms-access表中插入多行的SQL代码 [英] SQL code to insert multiple rows in ms-access table

问题描述

我正在尝试加快我的代码的速度，瓶颈似乎是通过ODBC从外部Access向Jet MDB的单独插入语句.我需要一次插入100行，并且必须重复多次.

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.

是否可以使用SQL代码在表中插入多行?这是我尝试过的一些方法，但是它们都不起作用.有什么建议吗?

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)

推荐答案

我在 R ，(我正在使用的软件). RODBC软件包具有函数sqlSave，该函数允许一次将整个data.frame追加到一个表中.这几乎是事务中单个插入的速度的两倍.

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)

这篇关于在ms-access表中插入多行的SQL代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！