msaccess multiple insert query
Hi
I have a query that is generated from a c# application and is updating an ms access database.... the generated query looks like this:
but access is flipping it's lid, saying it expects a ';' after the first value set... is access unable to accept multiple insert statements?
I have a query that is generated from a c# application and is updating an ms access database.... the generated query looks like this:
INSERT INTO pd_user_answers (field1,field2,field3,field4)
VALUES
(5, 1, 2, 1),
(5, 1, 2, 2),
(5, 1, 3, 3),
(5, 1, 1, 4),
(5, 1, 4, 5),
(5, 1, 1, 6),
(5, 1, 2, 7),
(5, 1, 4, 8),
(5, 1, 3, 9),
(5, 1, 4, 10),
(5, 1, 2, 75),
(5, 1, 1, 76),
(5, 1, 2, 77),
(5, 1, 2, 78),
(5, 1, 3, 79),
(5, 1, 4, 80),
(5, 1, 2, 81),
(5, 1, 2, 82),
(5, 1, 2, 83),
(5, 1, 4, 84),
(5, 1, 4, 85),
(5, 1, 3, 86),
(5, 1, 3, 87),
(5, 1, 1, 88),
(5, 1, 1, 89),
(5, 1, 2, 90),
(5, 1, 2, 91),
(5, 1, 1, 28),
(5, 1, 1, 29),
(5, 1, 3, 30),
(5, 1, 3, 31),
(5, 1, 4, 32),
(5, 1, 4, 33),
(5, 1, 4, 34),
(5, 1, 4, 35),
(5, 1, 1, 36),
(5, 1, 4, 37),
(5, 1, 4, 38),
(5, 1, 2, 39),
(5, 1, 2, 40),
(5, 1, 3, 41),
(5, 1, 3, 42),
(5, 1, 3, 43),
(5, 1, 3, 44),
(5, 1, 4, 45),
(5, 1, 4, 46),
(5, 1, 1, 47),
(5, 1, 1, 48),
(5, 1, 1, 49),
(5, 1, 1, 50),
(5, 1, 2, 51),
(5, 1, 2, 52),
(5, 1, 2, 53),
(5, 1, 3, 54),
(5, 1, 3, 55),
(5, 1, 4, 56),
(5, 1, 4, 57),
(5, 1, 4, 58),
(5, 1, 4, 59),
(5, 1, 1, 60),
(5, 1, 3, 61),
(5, 1, 3, 62);but access is flipping it's lid, saying it expects a ';' after the first value set... is access unable to accept multiple insert statements?
When you use Values it expects just one single insert.
You can try this:
tbl_name_from_your_db=use any table name from your db
You can try this:
tbl_name_from_your_db=use any table name from your db
INSERT INTO pd_user_answers (field1,field2,field3,field4)
SELECT (5, 1, 2, 1) FROM tbl_name_from_your_db
UNION SELECT (5, 1, 2, 2) FROM tbl_name_from_your_db
UNION SELECT (5, 1, 2, 2) FROM tbl_name_from_your_db
UNION SELECT (5, 1, 3, 3) FROM tbl_name_from_your_db
UNION SELECT (5, 1, 1, 4) FROM tbl_name_from_your_dbASKER
fyed
I had already used the looping method, but it was taking too much time to update, so was trying different approaches, and this one (multiple insert) is one I have used on web-based systems with mySQL, however this is access.... :-/
I had already used the looping method, but it was taking too much time to update, so was trying different approaches, and this one (multiple insert) is one I have used on web-based systems with mySQL, however this is access.... :-/
ASKER
hey sweetie
I am trying your suggestion but getting an error
access says
"Syntax error (comma) in query expression '(3,1,2,1)'."
I am trying your suggestion but getting an error
INSERT INTO pd_user_answers (field1,field2,field3,field4)
SELECT (3, 1, 2, 1) FROM pd_answers
UNION SELECT (3, 1, 4, 2) FROM pd_answersaccess says
"Syntax error (comma) in query expression '(3,1,2,1)'."
remove the ()
INSERT INTO pd_user_answers (field1,field2,field3,fiel
SELECT 3, 1, 2, 1 FROM pd_answers
UNION SELECT 3, 1, 4, 2 FROM pd_answers
INSERT INTO pd_user_answers (field1,field2,field3,fiel
SELECT 3, 1, 2, 1 FROM pd_answers
UNION SELECT 3, 1, 4, 2 FROM pd_answers
You would not use pd_answers as the 'From' table name - that is where you are trying to insert the records.
You need a table containing one record as the 'From' table (if it has more then you will insert one for each record).
You need a table containing one record as the 'From' table (if it has more then you will insert one for each record).
ASKER
hey peters57r
the from table is different, however it does have more than one record so i will create a new table just for this....
the from table is different, however it does have more than one record so i will create a new table just for this....
ASKER
ok, changed to the following
no access is saying
"Syntax error in FROM clause"
and highlighting the word UNION
INSERT INTO pd_user_answers (fieldlist)
SELECT 3, 1, 2, 1 FROM pd_temp
UNION SELECT 3, 1, 4, 2 FROM pd_tempno access is saying
"Syntax error in FROM clause"
and highlighting the word UNION
Sorry - yes I see now- I had just scanned quickly and didn't pick up the difference.
The insert goes into pd_user_answers and the 'fake' select from table is pd_answers . It doesn't really matter how many records are in pd_answers
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
thanks for the help... I have gone back to my original 'looping' method... it's simpler and cleaner...
;o)
;o)
I believe that your code is going to have to generate multiple full statements.
INSERT INTO pd_user_answers (field1,field2,field3,fiel
'execute that, then
INSERT INTO pd_user_answers (field1,field2,field3,fiel
'execute that, then
You may be able to separate those with semi-colons and execute it as multiple statements in a single query (as you can with SQL Server), but I have never tried that.