Professional Web Applications Themes

Inserting multiple records - ASP Database

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?...

  1. #1

    Default Inserting multiple records

    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?


    dave Guest

  2. #2

    Default Re: Inserting multiple records

    dave wrote: 

    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"


    Bob Guest

  3. #3

    Default Re: Inserting multiple records

    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','valA 2','valB2','valC2'

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

    Cheers



    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > 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"
    >
    >[/ref]


    dave Guest

  4. #4

    Default Re: Inserting multiple records

    No:

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

    Open your database in Access, open the query builder (create a new query in
    Design View) and switch to SQL View without choosing a table, Copy in the
    Select clause of te above query (everything after the Insert clause), run it
    and see what you get.

    Bob Barrows

    dave wrote: 
    >>
    >> 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"[/ref][/ref]

    --
    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"


    Bob Guest

  5. #5

    Default Re: Inserting multiple records

    Hmm - no joy

    Test database called "Table1"

    3 columns ID (autonumber), field1(text), field 2(text)

    Insert Into table1 (field1,field2)
    Select 'val1','val1'
    Union All Select 'valA1','valB1'
    Union All Select 'valA2','valB2'

    Syntax Error - Missing operator in query expression "val1' Union All Select
    'valA1"

    Any good ideas for me? :-)



    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    in 
    it [/ref]
    >
    > --
    > 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"
    >
    >[/ref]


    dave Guest

  6. #6

    Default Re: Inserting multiple records

    Your attempt would have worked fine in SQL Server, Unfortunately, Access
    (Jet) makes you jump through some hoops. While this works perfectly fine:

    Select 'val1','val1'

    This doesn't::

    Select 'val1','val1'
    Union All Select 'valA1','valB1'

    I get a "query input must contain one table or query" error when I try it in
    Access. I had to use the old kludge we used back in the Access 97 days:


    Insert Into table1 (field1,field2)
    Select * From (
    Select top 1 'val1','val1' from msysobjects
    Union All Select top 1 'valA1','valB1' from msysobjects
    Union All Select top 1 'valA2','valB2' from msysobjects) as q

    This works in my test.

    Bob Barrows

    dave wrote: 


    --
    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"


    Bob Guest

Similar Threads

  1. Problem inserting records
    By TCrimson in forum Macromedia Dynamic HTML
    Replies: 3
    Last Post: May 13th, 05:05 AM
  2. inserting records
    By tapping in forum Coldfusion Database Access
    Replies: 5
    Last Post: September 29th, 02:19 PM
  3. Web Service CFC: Inserting multiple records
    By GiantGinkgo in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: July 18th, 01:57 PM
  4. Error inserting records
    By Vincent in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 1st, 01:13 AM
  5. Inserting blank records
    By david preswick in forum FileMaker
    Replies: 1
    Last Post: August 31st, 02:55 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139