Acces - insert multiple rows in a single sql-insert

[M@rtin]

Hi!

I would like to know if it's possible (in Access 2007) to make a single
"insert into" in a table, with multiple rows specified ?
Something like:
INERT INTO MYTABLE VALUES('1','2','3'),('4','5','6'),('7','8','9');

I couldn't make it work in Access 2003, and it's very annoying since my
program now is making INSERT INTO in a loop, with single rows, which is VERY
time consuming when the number of loops are large. It works fine in
MSSQL-server.

Thanks,

Martin

[Danny Lesandrini]

You can do this, but you need to use a UNION SELECT instead of VALUES ...

INSERT INTO MyTable (fld02, fld02, fld03)
SELECT '1','2','3'
UNION SELECT '4','5','6'
UNION SELECT '7','8','9'

--
Danny J Lesandrini
dlesa...@hotmail.com
www.amazecreations.com

"M@rtin" <M@rtin@discussions.microsoft.com> wrote in message
news:0263C1BF-7499-46B2...@microsoft.com...

[M@rtin]

Thanks for your reply, but have you tried this yourself?

This works:
INSERT INTO MyTable (fld1,fld2,fld03)

SELECT '1','2','3'

But this doesn't:
INSERT INTO MyTable (fld1, fld2, fld3, fld4, fld5)

SELECT '1','2','3'
UNION SELECT '4','5','6'
UNION SELECT '7','8','9'

The error I get is:
Syntax error (missing operator) in query expression...

The problem occurs after the first SELECT. Access seems to be unable to
interprete the rest of the query. Solutions anyone?

[M@rtin]

Correction:

Thanks for your reply, but have you tried this yourself?
>
> This works:
> INSERT INTO MyTable (fld1,fld2,fld03)
> SELECT '1','2','3'
>
> But this doesn't:

> INSERT INTO MyTable (fld1, fld2, fld3)

[Brendan Reynolds]

"M@rtin" <Mr...@discussions.microsoft.com> wrote in message
news:B9C9CDBE-DB56-4D32...@microsoft.com...

As far as I can tell (and I haven't looked into this in any great detail, so
I could very easily be wrong) JET SQL appears to require a FROM clause in
this context. Try something like this ...

INSERT INTO MyTable (fld1, fld2, fld3)

SELECT '1','2','3' FROM SomeTable
UNION SELECT '4','5','6' FROM SomeTable
UNION SELECT '7','8','9' FROM SomeTable

--
Brendan Reynolds

[M@rtin]

Thank you Brendan,

The problem is that I'm not retrieving those values from any tables, so I
can't use "FROM"...

/Martin

[Rick Brandt]

On Thu, 19 Feb 2009 03:06:19 -0800, M@rtin wrote:

> Thank you Brendan,
>
> The problem is that I'm not retrieving those values from any tables, so
> I can't use "FROM"...

Just pick any table and it should work. Might be better to choose a
smaller one. Normally that would produce a row of your literals per row
in the table used in the FROM, but the UNION will eliminate duplicates.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[M@rtin]

Sorry, still no progress.

Works:

INSERT INTO MyTable (fld1, fld2, fld3)

SELECT '1','2','3' FROM FILE

Doesn't work:

INSERT INTO MyTable (fld1, fld2, fld3)

SELECT '1','2','3' FROM FILE
UNION SELECT '4','5','6' FROM FILE

Error: Syntax error in FROM clause

To me it seems more and more like a multiple-row-insert is impossible in MS
Access 2k3... at least without fetching existing values from tables. Crap! Am
I really the first developer trying to insert multiple rows with values from
variables instead of tables??? Double-crap!

/Martin

[Blessed Tabvirwa]

Because this works:

INSERT INTO MyTable (fld1, fld2, fld3)
SELECT '1','2','3' FROM FILE

You can do:

INSERT INTO MyTable (fld1, fld2, fld3)

SELECT *
FROM (select top 1 "b1a" AS fld1, "b2a" AS fld2, "b3a" AS fld3 from dummytable
union all
select top 1 "b1b" AS fld1, "b2b" AS fld2, "b3b" AS fld3 from dummytable
union all
select top 1 "b1c" AS fld1, "b2c" AS fld2, "b3c" AS fld3 from dummytable)

BUT you need to make sure of the following:
1) The dummy table "dummytable" MUST already exist in your access DB
2) The dummy table "dummytable" MUST have at least 1 row of data
3) The "top 1" ensures you don't get repeats for a table with more than one row

But again, it might be easier just to do separate inserts, especially if you are already building things up in a loop.

> On Monday, January 26, 2009 8:52 AM M@rtin@discussions.microsoft.com wrote:

> Hi!
>
> I would like to know if it's possible (in Access 2007) to make a single
> "insert into" in a table, with multiple rows specified ?
> Something like:
> INERT INTO MYTABLE VALUES('1','2','3'),('4','5','6'),('7','8','9');
>
> I couldn't make it work in Access 2003, and it's very annoying since my
> program now is making INSERT INTO in a loop, with single rows, which is VERY
> time consuming when the number of loops are large. It works fine in
> MSSQL-server.
>
> Thanks,
>
> Martin

>> On Monday, January 26, 2009 8:57 AM Danny Lesandrini wrote:

>> You can do this, but you need to use a UNION SELECT instead of VALUES ...
>>
>>
>> INSERT INTO MyTable (fld02, fld02, fld03)

>> SELECT '1','2','3'

>> UNION SELECT '4','5','6'

>> UNION SELECT '7','8','9'
>>

>> --
>> Danny J Lesandrini
>> dlesa...@hotmail.com
>> www.amazecreations.com
>>
>>
>>
>> "M@rtin" <M@rtin@discussions.microsoft.com> wrote in message
>> news:0263C1BF-7499-46B2...@microsoft.com...

>>> On Thursday, February 19, 2009 5:23 AM Mrti wrote:

>>> Thanks for your reply, but have you tried this yourself?
>>>
>>> This works:
>>> INSERT INTO MyTable (fld1,fld2,fld03)

>>> SELECT '1','2','3'
>>>

>>> But this doesn't:
>>> INSERT INTO MyTable (fld1, fld2, fld3, fld4, fld5)

>>> SELECT '1','2','3'

>>> UNION SELECT '4','5','6'

>>> UNION SELECT '7','8','9'
>>>
>>> The error I get is:
>>> Syntax error (missing operator) in query expression...
>>>
>>> The problem occurs after the first SELECT. Access seems to be unable to
>>> interprete the rest of the query. Solutions anyone?

>>>> On Thursday, February 19, 2009 5:27 AM Mrti wrote:

>>>> Correction:
>>>>
>>>> Thanks for your reply, but have you tried this yourself?

>>>>> On Thursday, February 19, 2009 5:52 AM Brendan Reynolds wrote:

>>>>> "M@rtin" <Mr...@discussions.microsoft.com> wrote in message
>>>>> news:B9C9CDBE-DB56-4D32...@microsoft.com...
>>>>>
>>>>>

>>>>> As far as I can tell (and I haven't looked into this in any great detail, so
>>>>> I could very easily be wrong) JET SQL appears to require a FROM clause in
>>>>> this context. Try something like this ...
>>>>>

>>>>> INSERT INTO MyTable (fld1, fld2, fld3)

>>>>> SELECT '1','2','3' FROM SomeTable
>>>>> UNION SELECT '4','5','6' FROM SomeTable
>>>>> UNION SELECT '7','8','9' FROM SomeTable
>>>>>
>>>>> --
>>>>> Brendan Reynolds

>>>>>> On Thursday, February 19, 2009 6:06 AM Mrti wrote:

>>>>>> Thank you Brendan,
>>>>>>
>>>>>> The problem is that I am not retrieving those values from any tables, so I
>>>>>> cannot use "FROM"...
>>>>>>
>>>>>>
>>>>>> /Martin
>>>>>>
>>>>>> "Brendan Reynolds" wrote:

>>>>>>> On Thursday, February 19, 2009 7:31 AM Rick Brandt wrote:

>>>>>>> On Thu, 19 Feb 2009 03:06:19 -0800, M@rtin wrote:
>>>>>>>
>>>>>>>
>>>>>>> Just pick any table and it should work. Might be better to choose a
>>>>>>> smaller one. Normally that would produce a row of your literals per row
>>>>>>> in the table used in the FROM, but the UNION will eliminate duplicates.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Rick Brandt, Microsoft Access MVP
>>>>>>> Email (as appropriate) to...
>>>>>>> RBrandt at Hunter dot com

>>>>>>>> On Thursday, February 19, 2009 8:40 AM Mrti wrote:

>>>>>>>> Sorry, still no progress.
>>>>>>>>
>>>>>>>> Works:
>>>>>>>> INSERT INTO MyTable (fld1, fld2, fld3)
>>>>>>>> SELECT '1','2','3' FROM FILE
>>>>>>>>
>>>>>>>> Doesn't work:
>>>>>>>> INSERT INTO MyTable (fld1, fld2, fld3)
>>>>>>>> SELECT '1','2','3' FROM FILE
>>>>>>>> UNION SELECT '4','5','6' FROM FILE
>>>>>>>>
>>>>>>>> Error: Syntax error in FROM clause
>>>>>>>>
>>>>>>>> To me it seems more and more like a multiple-row-insert is impossible in MS
>>>>>>>> Access 2k3... at least without fetching existing values from tables. Crap! Am
>>>>>>>> I really the first developer trying to insert multiple rows with values from
>>>>>>>> variables instead of tables??? Double-crap!
>>>>>>>>
>>>>>>>> /Martin
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> "Rick Brandt" wrote:

>>>>>>>> Submitted via EggHeadCafe
>>>>>>>> SharePoint 2010 Visual Web Parts using Visual Studio 2010, Feature Designer and Package Designer
>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/aef26be2-b36c-4316-b1b7-e55c8b61646b/sharepoint-2010-visual-web-parts-using-visual-studio-2010-feature-designer-and-package-designer.aspx