How to insert multiple rows in Microsoft Access tables?

Question

I have tried every way I can find with Google to insert multiple rows in Access with queries.
None of the these are working for me:

INSERT INTO MyTable (fld02, fld02, fld03)
SELECT '1','2','3'
UNION SELECT '4','5','6'
UNION SELECT '7','8','9'

INSERT INTO faculty1 (first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise)
VALUES ('a','b','Male','January 15, 1955','4202 E Fowler Ave','Tampa','813-974-2268','50000','Computer Engineering');
VALUES ('c','d','Male','October 22, 1948','4202 E Fowler Ave','Tampa','813-974-1538','80000','Computer Engineering');
VALUES ('e','f','Male','May 15, 1933','4202 E Fowler Ave','Tampa','813-974-4425','120000','Computer Engineering');
VALUES ('g','h','Female','December 3, 1960','4202 E Fowler Ave','Tampa','813-974-1276','60000','Computer Engineering');
VALUES ('i','j','Female','November 17, 1962','4202 E Fowler Ave','Tampa','813-974-2154','62000','Computer Engineering');


INSERT INTO faculty1 (first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise)
VALUES ('a','b','Male','January 15, 1955','4202 E Fowler Ave','Tampa','813-974-2268','50000','Computer Engineering');

INSERT INTO faculty1 (first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise)
VALUES ('c','d','Male','October 22, 1948','4202 E Fowler Ave','Tampa','813-974-1538','80000','Computer Engineering');

INSERT INTO faculty1 (first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise)
VALUES ('e','f','Male','May 15, 1933','4202 E Fowler Ave','Tampa','813-974-4425','120000','Computer Engineering');

INSERT INTO faculty1 (first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise)
VALUES ('g','h','Female','December 3, 1960','4202 E Fowler Ave','Tampa','813-974-1276','60000','Computer Engineering');

INSERT INTO faculty1 (first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise)
VALUES ('i','j','Female','November 17, 1962','4202 E Fowler Ave','Tampa','813-974-2154','62000','Computer Engineering');

I even tried a single entry, and didn't work either.

INSERT INTO faculty1 ( first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise )
VALUES ('a', 'b', 'Male', 'January 15, 1955', '4202 E Fowler Ave', 'Tampa', '813-974-2268', '50000', 'Computer Engineering');

Is there something wrong with my CREATE statement?

CREATE TABLE faculty1
(
    facultynumber int PRIMARY KEY,
    first_name CHAR(20),
    last_name CHAR(20),
    gender CHAR(10),
    birthdate CHAR(25),
    address CHAR(50),
    city CHAR(20),
    phone CHAR(20),
    salary INTEGER,
    area_of_expertise CHAR(20)
);

http://www.pixhost.org/show/1360/23620036_access_error.jpg

This surprisingly works. Is Access not able to handle you not giving a primary key? I thought this was optional.

http://www.w3schools.com/sql/sql_insert.asp

INSERT INTO faculty1 ( facultynumber, first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise )
VALUES ('5', 'a', 'b', 'Male', 'January 15, 1955', '4202 E Fowler Ave', 'Tampa', '813-974-2268', '50000', 'Computer Engineering');

What happens when you try to insert a single record? You say it doesn't work but do you get en error message? — Ola Ekdahl, Oct 23 '14 at 05:16
@JorgeCampos sorry I thought I attached a screenshot. It is now attached. It seems like microsoft access wants you to give a primary. it worked when I gave a primary. — cokedude, Oct 23 '14 at 05:42
@OlaEkdahl sorry I thought I attached a screenshot. It is now attached. — cokedude, Oct 23 '14 at 05:42

Shiffty · Answer 1 · 2014-10-23T06:17:33.897

0

Salary is defined as an integer but you are trying to insert it with quotes. Also you have to either provide a faculty number or make it identity / counter

INSERT INTO faculty1 (facultynumber, first_name, last_name, gender, birthdate, address, city, phone, salary, area_of_expertise )
VALUES (1, 'a', 'b', 'Male', 'January 15, 1955', '4202 E Fowler Ave', 'Tampa', '813-974-2268', 50000, 'Computer Engineering');

edited Oct 23 '14 at 06:17

answered Oct 23 '14 at 05:25

Shiffty

2,086
2
26
31

I copied and pasted exactly like you have it and got this error. http://www.pixhost.org/show/1360/23620060_access_error1.jpg – cokedude Oct 23 '14 at 05:51

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

If you want the primary key to be automatically generated, you need to define the column as an Autonumber type.

CREATE TABLE  faculty1
(
  facultynumber PRIMARY KEY AUTOINCREMENT,
  ...

More Detail in this thread: https://stackoverflow.com/a/1072938/2712185

edited May 23 '17 at 11:51

Community

1
1

answered Oct 24 '14 at 20:52

LungFungus

141
1
4

How to insert multiple rows in Microsoft Access tables?

2 Answers2