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 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"
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]"
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"
>
>
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:
> 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]"
> 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"
--
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"
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]"
news:ehwf6I3vEHA.3088@TK2MSFTNGP12.phx.gbl...
> 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:
> > 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]"
> > 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"
>
> --
> 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"
>
>
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:
> 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"
--
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"