Is there any way to create multiple insert statements in a ms-access query?

by

yes and no.

You can’t do:

insert into foo (c1, c2, c3)
values ("v1a", "v2a", "v3a"),
       ("v1b", "v2b", "v3b"),
       ("v1c", "v2c", "v3c")

but you can do

insert into foo (c1, c2, c3)
    select (v1, v2, v3) from bar

What does that get you if you don’t already have the data in a table? Well, you could craft a Select statement composed of a lot of unions of Selects with hard coded results. 

INSERT INTO foo (f1, f2, f3)
    SELECT *
    FROM (select top 1 "b1a" AS f1, "b2a" AS f2, "b3a" AS f3 from onerow
    union all
    select top 1 "b1b" AS f1, "b2b" AS f2, "b3b" AS f3 from onerow
    union all 
    select top 1 "b1c" AS f1, "b2c" AS f2, "b3c" AS f3 from onerow)

Note: I also have to include a some form of a dummy table (e.g., onerow) to fool access into allowing the union (it must have at least one row in it), and you need the “top 1” to ensure you don’t get repeats for a table with more than one row

But then again, it would probably be easier just to do three separate insert statements,
especially if you are already building things up in a loop (unless of course the cost of doing the inserts is greater than the cost of your time to code it).

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. “General error Unable to open registry key Temporary (volatile) …” from Access ODBC
  6. Is there a simple way of populating dropdown in this Access Database schema?
  7. create form to add records in multiple tables
  8. Operation must use an updatable query. (Error 3073) Microsoft Access
  9. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  10. Running Microsoft Access as a Scheduled Task
  11. How to diagnose MS access crashes
  12. “Operation must use an updateable query” error in MS Access
  13. How to restart counting from 1 after erasing table in MS Access?
  14. Equivalent cURL in VBA?
  15. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  16. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  17. MS Access trigger?
  18. Insert SQL command with Datetime in MS-Access
  19. Is MS Access (JET) suitable for multiuser access?
  20. VBA procedure to import csv file into access
  21. How to select top 10 in Access query?
  22. ConcatRelated() in Access, using two keys
  23. Concatenating multiple rows into single line in MS Access [duplicate]
  24. How to write Case Sensitive Query for MS Access?
  25. Transform multiple rows into columns with unique key
  26. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  27. Create sequential ID value based on the year that a record is added
  28. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  29. How to execute a query in ms-access in VBA code?
  30. Force all users to disconnect from 2010 Access backend database

Leave a Comment