INSERT INTO with multiple rows

[Ecron]

I am trying to insert into a table using the SQL INSERT INTO statement.
The table I am trying to insert into is called t_comments. That table has 8 columns.

This code works for 1 record:

INSERT INTO t_comments VALUES (1,2,3,4,5,6,7,8);

But, if I attempt to enter into multiple records:

INSERT INTO t_comments VALUES (1,2,3,4,5,6,7,8), (9,10,11,12,13,14,15,16), (17,18,19,20,21,22,23,24);

I am assigning each of those statements to a string and then executing with DoCmd.RunSQL

The error I get for the multiple row attempt is:

"Run-time error '3137': Missing semicolon (;) at end of SQL statement."

And, as you can see. I have a semi-colon.

Thanks.

 

[Ecron]

http://msdn.microsoft.com/en-us/library/bb208861.aspx

I found that article. I'm hoping it will help me!

 

[DJkarl]

I'm 99% positive that the VALUES clause can only ever insert one record at a time. If you have multiple records to add you should be appending from a table or another query I'd think.

http://www.firstsql.com/tutor4.htm

 

[Ecron]

Yah.

When I checked out the msdn article I came to that conclusion. I will just have to break them into individual INSERT statements.

Thank you!

 

[redneckgeek]

Here's a "cheat" that works. Use SELECT instead of VALUES:

INSERT INTO SOME_TABLE (FIELD1, FIELD2)
SELECT DISTINCT 1,2 FROM ANY_TABLE
UNION ALL
SELECT DISTINCT 3,4 FROM ANY_TABLE
UNION ALL
SELECT DISTINCT 5,6 FROM ANY_TABLE

A few things:
Use a small table (few rows) for ANY_TABLE. You are not really pulling any data from that table, but access insists that you have a from statement.
It will return one row for each row in the table, that's why the DISTINCT is in there. So if ANY_TABLE has 3 rows, the above (without DISTINCT) would return:
1,2
1,2
1,2
3,4
3,4
3,4
5,6
5,6
5,6

 

[LPurvis]

I'd agree - that using a small table is best - just to minimise the impact all round.
However regardless - I'd still suggest that the only real requirement be that the table have a primary key and use

SELECT TOP 1 1, 2 FROM ANY_TABLE
UNION ALL
ETC....

As the TOP predicate should be faster anyway - and very, very much so if, for any reason, a non-small table were referenced.
Just a small point really.