CODEWITHSUNDEEP
sqlms-access
Josh
Josh

Reputation: 66

Access SQL - Insert multiple rows wont work

INSERT INTO EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
VALUES ('101', 'News', 'John', 'G', '08-Nov-00', '502'),
('102', 'Senior', 'David', 'H', '12-Jul-89', '501');

I've been searching for quite some time and most people say to but the comma between the two sets but when I do that it says that it is missing a ; at the end of the SQL statement so then I separate them with the ; and get the error that talks about code after the semi-colon, which is understandable.

I've even tried SELECT and UNIONALL to no avail and was just checking out this but it still recommended the SELECT and UNIONALL. Is there something I'm missing completely?

I'm using MS Access 2016, thanks in advance for the help.

Upvotes: 1

Views: 5965

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

MS Access does not permit multiple rows being inserted with a single insert . . . values. I think the "typical" MS Access solution is:

INSERT INTO EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
    VALUES ('101', 'News', 'John', 'G', '08-Nov-00', '502');
INSERT INTO EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
    VALUES ('102', 'Senior', 'David', 'H', '12-Jul-89', '501');

Upvotes: 2

Eric Lam
Eric Lam

Reputation: 359

The link you have given already state that you CANNOT do 

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

Which is exactly the way you are doing it now.

Try

INSERT INTO EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
    SELECT *
    FROM (select top 1 "101" AS EMP_NUM, "News" AS EMP_LNAME, "John" AS EMP_FNAME, "G" AS EMP_INITIAL, "08-Nov-00" AS EMP_HIREDATE, "502" AS JOB_CODE from onerow
    union all
    select top 1 "102" AS EMP_NUM, "Senior" AS EMP_LNAME, "David" AS EMP_FNAME, "H" AS EMP_INITIAL, "12-Jul-89" AS EMP_HIREDATE, "501" AS JOB_CODE from onerow)

I am not sure about MS-Access SQLs. But " is differ from ' in SQL. It seems that the link you give uses " as the answer. Why not give it a try? But generally ' should be used for string values.

Upvotes: 2

Related Questions