1
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.

Community
  • 1
  • 1
Josh
  • 66
  • 1
  • 9
  • Why do you need to do this? If it's going to be executed from somewhere (which it has to be), why not just execute multiple statements? – jleach Apr 09 '17 at 23:41
  • Multiple statements as in different queries or having two sets of code in the same query? Because I've tried doing that but it doesn't allow the second block of code. **Also** it must be in one query. – Josh Apr 09 '17 at 23:42
  • Maybe this will help you out. http://stackoverflow.com/questions/1838593/how-do-i-execute-multiple-sql-statements-in-access-query-editor – Jason Apr 09 '17 at 23:52

2 Answers2

2

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.

Eric Lam
  • 329
  • 1
  • 5
  • 13
  • This works, thank you so much even though I don't fully understand the `select top 1`. I changed the `onerow` to the table it is linked to (foreign key) and it worked perfectly. – Josh Apr 10 '17 at 00:12
  • The TOP 1 is just used for limiting the result to the First one. I'm glad that it helps :) – Eric Lam Apr 10 '17 at 00:18
  • 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 foo return Empty when the table is empty, not work. – Ejrr1085 Oct 29 '20 at 23:01
2

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');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786