Is there any way to create multiple insert stateme

Question

I am using MS Access 2003. I want to run a lot of insert SQL statements in what is called 'Query' in MS Access. Is there any easy(or indeed any way) to do it?

Answer 1

@Rik Garner: Not sure what you mean by 'batch' but the

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)

construct, although being a single SQL statement, will actually insert each row one at a time (rather than all at once) but in the same transaction: you can test this by adding a relevant constraint e.g.

ALTER TABLE foo ADD
   CONSTRAINT max_two_foo_rows
      CHECK (2 >= (SELECT COUNT(*) FROM foo AS T2));

Assuming the table is empty, the above INSERT INTO..SELECT.. should work: the fact it doesn't is because the constraint was checked after the first row was inserted rather than the after all three were inserted (a violation of ANSI SQL-92 but that's MS Access for you ); the fact the table remains empty shows that the internal transaction was rolled back.

@David W. Fenton: you may have a strong personal preference for DAO but please do not be too hard on someone for choosing an alternative data access technology (in this case ADO), especially for a vanilla INSERT and when they qualify their comments with, " Off the top of my head, the code to do it should look something like…" After all, you can't use DAO to create a CHECK constraint :)