View Full Version : Bulk DB INSERT

07-07-2004, 06:56 PM
Hi All,
I am attempting to write a function that inserts about 5000 or more records at a time into an Access database table. I am reading a comma-delimited file to get the values, and then inserting them using an standard "FOR" loop. Each time I loop, I run a SQL Insert. The problem is, this is sooooo SLOOOOOW... not to mention poor design. Often the script times out. Unfortunately, I don't know any other method. Also, I must use Access. There is no SQL Server option, which would help, but there is also no load on this app... no concurrent users.

Any ideas?

My code (lots of other stuff, but note the 'INSERT INTO' below):

SplitValues = split(SplitLines(counter), ",")
For values = 0 to ubound(SplitValues)

SUAVLookup = "SELECT * FROM SiteUserAttributeValue WHERE SiteID = " & session("siteID") & " AND UserID like '" & UID & "' AND AttributeName like '" & SplitCols(values) & "' ORDER BY USerID ASC"
Cmd.CommandText = SUAVLookup
Set Rs = Cmd.Execute
SUAVCount = 0
While not Rs.EOF
SUAVCount = SUAVCount + 1

if SUAVCount = 0 then

SUAV = "INSERT INTO SiteUserAttributeValue (SiteID,UserID,AttributeName,ValueChar200) VALUES ("
SUAV = SUAV & "'" & session("siteID") & "','" & UID & "','" & SplitCols(values) & "','" & SplitValues(values) & "')"

Cmd.CommandText = SUAV
Set Rs = Cmd.Execute
'response.write SUAV & "<br>"

end if


07-07-2004, 10:05 PM
have you tried using:
Server.ScriptTimeout = 1800

thats in seconds,, so 1800 = 30 minutes.

Roy Sinclair
07-09-2004, 09:15 PM
Have you considered creating an ADO RecordSet against the Access database and then using the ADO methods to insert records to that dataset? That way ADO can perform the insert SQL statements and may use a more efficient method than you're using, plus there are some properties you can set to condition ADO to handling bulk inserts which should help.

You're already getting the recordset so try making sure it's an "updateable" one and see how it goes.