使用Vbscript将记录批量插入Access中 [英] Bulk insert records into Access using Vbscript
问题描述
我真的很喜欢这根头发。我有一个vbscript,我试图将几十万条记录插入到Access数据库中。
I'm really pulling my hair out with this one. I've got a vbscript and I'm trying to insert a few hundred thousand records into an Access database.
很明显,如果我一次在一个数据库中做一个记录真的很慢时间,所以我认为我可以通过某种交易批量插入它们。所以我试着这样写:
Obviously, it's really slow if I do them one at a time, so I thought I might be able to bulk insert them with some sort of transaction. So I tried writing this:
set rs = CreateObject("ADODB.recordset")
rs.Open "table", objConn,, 4
For counter = 1 to 100000
rs.AddNew
rs("username") = "Value"
Next
rs.UpdateBatch
(objConn是数据库连接)。
(objConn is the database connection).
问题是我收到一条错误消息:
The problem is I get an error saying:
具有待处理更改的行数超过了限制
"Number of rows with pending changes exceeded the limit"
我得到的是,当有多个待更改项时。
and I get that when there's more than one pending change.
我想我的交易设置不正确,但是有点卡住了。难道有人可以指出我的方式的错误吗?非常感谢。
I guess I haven't set my transaction up correctly, but I'm a bit stuck. Don't suppose someone could point out the error of my ways? Thanks very much.
推荐答案
为了为我的建议在事务中使用命令辩护,我编写了
这个脚本:
To argue for my proposal to use a command in a transaction, I wrote this script:
Dim sAct : sAct = "trout"
If goWAN.Exists( "a" ) Then sAct = goWAN( "a" )
Dim nRecs : nRecs = 10
If goWAN.Exists( "n" ) Then nRecs = CLng( goWAN( "n" ) )
Dim sMFSpec : sMFSpec = goFS.GetAbsolutePathName( "..\data\ut.mdb" )
Dim oConn : Set oConn = CreateObject( "ADODB.Connection" )
Dim oRs : Set oRs = CreateObject( "ADODB.Recordset" )
Dim nRec, oCmd, nRA, aData, oParm
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sMFSpec
Set oRs.ActiveConnection = oConn
oConn.Execute( "DELETE FROM tLines" )
WScript.Echo "#Recs:", oConn.Execute( "SELECT COUNT(SampleText) FROM tLines" ).Fields( 0 )
WScript.Echo sAct
Select Case sAct
Case "trout"
Case "bob"
oRs.CursorLocation = adUseClient
oRs.CursorType = adOpenKeySet
oRs.LockType = adLockBatchOptimistic
Case "eh"
End Select
WScript.Echo "oRs.CursorLocation: ", oRs.CursorLocation
WScript.Echo "oRs.CursorType: ", oRs.CursorType
WScript.Echo "oRs.LockType: ", oRs.LockType
Select Case sAct
Case "trout", "bob"
oRs.Open "tLines", oConn, , adLockBatchOptimistic
For nRec = 1 to nRecs
oRs.AddNew
oRs( "SampleText" ) = "This is line " & nRec
Next
oRs.UpdateBatch
oRs.Close
Case "eh"
oConn.BeginTrans
Set oParm = CreateObject( "ADODB.Parameter" )
With oParm
.Name = "A"
.Type = adVarChar
.Value = ""
.Direction = adParamInput
.Size = 100
End With
Set oCmd = CreateObject( "ADODB.Command" )
With oCmd
Set .ActiveConnection = oConn
.CommandText = "INSERT INTO tLines (SampleText) VALUES (?)"
.CommandType = adCmdText
.Parameters.Append oParm
End With
ReDim aData( 0 )
For nRec = 1 to nRecs
aData( 0 ) = "This is line " & nRec
oCmd.Execute nRA, aData, adExecuteNoRecords + adCmdText
Next
oConn.CommitTrans
End Select
WScript.Echo "#Recs:", oConn.Execute( "SELECT COUNT(SampleText) FROM tLines" ).Fields( 0 )
WScript.Echo "First:", oConn.Execute( "SELECT TOP 1 * FROM tLines" ).Fields( 0 )
oConn.Close
用/ n:200和/ a:trout调用它显示:
called with /n:200 and /a:trout it shows:
#Recs: 0
trout
oRs.CursorLocation: 2
oRs.CursorType: 0
oRs.LockType: 1
... xpl.vbs(246, 11) Provider: Number of rows with pending changes exceeded the limit.
所以我想,我正确地再现了您的问题。对于/ a:bob:
So I think, I reproduced your problem correctly. For /a:bob:
#Recs: 0
bob
oRs.CursorLocation: 3
oRs.CursorType: 1
oRs.LockType: 4
#Recs: 200
First: This is line 1
xpl.vbs: Erfolgreich beendet. (0) [ 19.74219 secs ]
因此设置
oRs.CursorLocation = adUseClient
oRs.CursorType = adOpenKeySet
oRs.LockType = adLockBatchOptimistic
Bob(和Microsoft)建议的方法是解决您的问题的一种方法。为了提高速度,我在事务中输入了
命令:
as Bob (and Microsoft) adviced is one solution to your problem. To get some speed, I put an command into a transaction:
oConn.BeginTrans
Set oCmd = CreateObject( "ADODB.Command" )
...
ReDim aData( 0 )
For nRec = 1 to nRecs
aData( 0 ) = "This is line " & nRec
oCmd.Execute nRA, aData, adExecuteNoRecords + adCmdText
Next
oConn.CommitTrans
结果:
#Recs: 0
eh
oRs.CursorLocation: 2
oRs.CursorType: 0
oRs.LockType: 1
#Recs: 200
First: This is line 1
xpl.vbs: Erfolgreich beendet. (0) [ 1.47656 secs ]
从20到2秒(没有任何属性摆弄)似乎还不错给我。
From 20 to 2 secs (without any properties fiddling) seems not bad to me.
这篇关于使用Vbscript将记录批量插入Access中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!