Insert multiple rows with one insert...

[PogoWolf]

I'm trying to figure out if there's a way to insert muliple records into a database but only use one (large?) SQL statement?

For example:
if I have this data to insert:

Table1 schema Example:
Date
Time
Number
Yes_no
Question
Answer

Data Example:
01/13/05 00:13:34 12 1 "will this work?" "perhaps"
01/12/05 00:13:36 12 1 "will this work?" "No"
01/11/05 00:13:37 12 1 "will this work?" "yes"
01/09/05 00:13:38 12 1 "will this work?" "Maybe"
01/13/05 00:13:56 12 1 "will this work?" "No clue"

How can I instert all of this data with ONE insert statement?

The PogoWolf

http://pogowolf.GamersCommune.com

 

[PHV]

INSERT INTO Table1 ([Date],[Time],[Number],Yes_no,Question,Answer)
SELECT A.F1,A.F2,A.F3,A.F4,A.F5,A.F6 FROM (
SELECT #01/13/05# AS F1,#00:13:34# AS F2,12 AS F3,1 AS F4,'will this work?' AS F5,'perhaps' AS F6 FROM Table1
UNION SELECT #01/13/05#,#00:13:36#,12,1,'will this work?','No' FROM Table1
...
UNION SELECT #01/13/05#,#00:13:56#,12,1,'will this work?','No clue' FROM Table1
) AS A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PogoWolf]

Well it looks like that should work.. in Access I get An Append 0 Rows error...


The PogoWolf

http://pogowolf.GamersCommune.com

 

[PHV]

Append 0 Rows error
I guess Table1 is empty.
I suggest to create a dummy with a single row and do all the union select from this dummy table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244