Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Bulk DB INSERT

  1. #1
    New to the CF scene
    Join Date
    Jul 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Bulk DB INSERT

    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
    Rs.MoveNext
    Wend

    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

    Next

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    have you tried using:
    Server.ScriptTimeout = 1800

    thats in seconds,, so 1800 = 30 minutes.

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •