Re: Inserting multiple records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: dave (none_at_invalid.com)
Date: 10/31/04

  • Next message: Bob Barrows [MVP]: "Re: Inserting multiple records" 
    Date: Mon, 1 Nov 2004 06:32:52 +1300

    Thanks Bob, yes using 2k & Jet4

    Not quite sure I follow yet, but using that logic, and assuming I have an
    array of values, how would this go?

    sSQL = "Insert Into table (A,B,C) Union All Select
    'valA','valB','valC','valA1','valB1','valC1','valA2','valB2','valC2'

    My inputed values get delimited as above? (All text)

    Cheers

    "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
    news:OGcHLZ2vEHA.1296@TK2MSFTNGP10.phx.gbl...
    > dave wrote:
    > > Using Access, is there an easier, or less intensive way of inserting
    > > many records into the database at the same time?
    > >
    > > eg - insead of doing this:
    > >
    > > objConn.Execute("Insert INTO .....")
    > > objConn.Execute("Insert INTO .....")
    > > objConn.Execute("Insert INTO .....")
    > > objConn.Execute("Insert INTO .....")
    > > objConn.Execute("Insert INTO .....")
    > > objConn.Execute("Insert INTO .....")
    > >
    > > Is there a faster / better way?
    >
    > You can turn it into a single call to the database by constructing a union
    > query. The following example works with A2K and higher (Jet4). If you are
    > using an earlier version, a) you should have told us and b) there is a
    > workaround:
    >
    > dim sSQL, sUnion
    > sSQL = "Insert Into table (col1, ..., colN) "
    > 'start loop
    > 'populate value variables
    > if len(sSelect) = 0 then
    > sUnion="Select " & val1 & ... & valN
    > else
    > sUnion= sSelect & " Union All Select " & val1 & ... & valN
    > end if
    > 'end loop
    > sSQL = sSQL & sUnion
    > objConn.Execute sSQL,,1
    >
    > HTH,
    > Bob Barrrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >

  • Next message: Bob Barrows [MVP]: "Re: Inserting multiple records"

    Relevant Pages

    • Muti Dimmed Array Problem
      ... I am trying to create an array that will eventually loop to update ... corresponding record in a database. ... Prev by Date: ...
      (microsoft.public.scripting.vbscript)
    • Re: [PHP] Getting mysql_query results into an array
      ... Richard Lynch wrote: ... Simply loop through the results and do whatever you want to do with ... and don't put them into an array at all. ... However, I am currently writing an abstraction layer for a project that will later be ported from MySQL to another database, so I was thinking if some sort of "helper functions" might be useful, but I think simple wrappers are probably the way to go. ...
      (php.general)
    • Re: Read from text area
      ... to get it into an array. ... Then loop the array and split ... > batches, and these batches are generated on a local PC. ... and then run an asp/vbscript routine to update the database. ...
      (microsoft.public.inetserver.asp.general)
    • Re: [PHP] Getting mysql_query results into an array
      ... Simply loop through the results and do whatever you want to do with ... and don't put them into an array at all. ... This makes perfect sense. ... MySQL to another database (and I haven't even ...
      (php.general)
    • Re: KirbyBase
      ... creating objects from the database records was much easier. ... Hal, I don't know if you have had a chance to take a look at the beta yet, but I basically tried to implement a uniform way to specify one-to-one links, one-to-many links, and calculated fields in the ... I suppose it would in effect be embedding an array where all the ... My first couple of attempts at adding more complexity to KirbyBase did not honor this concept. ...
      (comp.lang.ruby)