MS Access insert multiple rows

Question

For a project I want to use a query to insert multiple rows into a table. I found several threads on how to do this, for example this one, that were really helpful but I still can't figure it out how to insert multiple rows.

The SQL code that I currently have doesn't cause any syntax error, but it just doesn't insert any rows.

My table that I want to insert into looks like:

create table SYT_ABRDAT
(
    id integer primary key not null,
    beginper integer,
    eindper integer,
    periode text,
    groep bit
)

The query that I'm currently using (I made it shorter):

insert into syt_abrdat (id, begindat, einddat, periode, groep) 
    select * 
    from
        (select top 1 
             "1" as id, "9999" as begindat, "9999" as einddat,
             "---" as periode, "1" as groep 
         from 
             onerow 
         union all
         select top 1 
             "2" as id, "9999" as begindat, "9999" as einddat,
             "XXX" as periode, "1" as groep 
         from 
             onerow
        )

Solution:

I added an empty row into table onerow instead of filling it with some data.

This is necessary

Igor · Accepted Answer · 2017-06-26T14:54:05.660

4

INSERT INTO syt_abrdat (id,begindat,einddat,periode,groep) 
SELECT * FROM 
   (SELECT TOP 1 1 AS id, 9999 AS begindat, 9999 as einddat, '---' as periode, 'WAAR' as groep FROM onerow UNION ALL
    SELECT TOP 1 2 AS id, 9999 AS begindat, 9999 as einddat, 'XXX' as periode, 'WAAR' as groep FROM onerow)

Comments:

Use single quotes for literal string values
Do not use quotes for literal numbers
the table onerow must contain at least 1 record

edited Jun 26 '17 at 14:54

answered Jun 26 '17 at 14:26

Igor

60,821
10
100
175

1

that would normally work in other databases but ms access won't allow multiple insert statements. You can trick this with a union. see: https://stackoverflow.com/questions/62504/is-there-any-way-to-create-multiple-insert-statements-in-a-ms-access-query/65027#65027 – B.Termeer Jun 26 '17 at 14:27
@B.Termeer - I missed that, thanks. Looking at that question are you sure that the table you are referencing (`onerow`) has 1 row? If there are none then this would be an expected result (no records inserted). – Igor Jun 26 '17 at 14:33
thanks for your response. I tried it but still get the problem that zero rows are added. I will check the table onerow. If i added one rowI will post the tabel structure of onerow – B.Termeer Jun 26 '17 at 14:33
@B.Termeer - the table structure of `onerow` does not matter as long as there is a record in the table. – Igor Jun 26 '17 at 14:34
1

thanks for your response. I added a empty row instead filling it with at least something. – B.Termeer Jun 26 '17 at 14:35

MS Access insert multiple rows

1 Answers1