inserting multiple rows with 1 query

sql ms-access ms-access-2003
noname picture noname · Dec 28, 2012 · Viewed 20.5k times · Source

I have problem in inserting multiple rows with 1 query using ms access 2003. When I use INSERT INTO like the code below

INSERT INTO Employee values ('1','b','c');
INSERT INTO Employee values ('2','d','e');

the problem, ms access always appears pop up characters found after end of SQL Statement. So, are there any way to insert the data into the table?

Answer

HansUp picture HansUp · Dec 28, 2012

With Access SQL you can't combine two INSERT statements. You could run each of them separately. But if you need to do it with a single statement, you will need to use a more complex query.

INSERT INTO Employee
SELECT '1','b','c'
FROM Dual
UNION ALL
SELECT '2','d','e'
FROM Dual;

Dual is a custom table designed to always contain only one row. You can create your own Dual table using the instructions from this Stack Overflow answer.

However, you don't actually need a custom table for this purpose. Instead of Dual, you can use any table or query which returns only one row.

Related questions

how to use distinct in ms access

I have two tables. Task and Categories. TaskID is not a primary key as there are duplicate values.When there are multiple contacts are selected for a specific task,taskid and other details will be duplicated.I wrote the query: …

Read More
How to find duplicates from two tables and also to find duplicate in itself?

I have created this statement in access 2003 SELECT COUNT(*) FROM TABLEA WHERE NOT EXISTS(SELECT * FROM TABLEB); Does this statement helps to check if the records in table A is the same as table b? TABLEA is the new table …

Read More
Adding values in two diff columns in ms access using sql query

Hey I was wondering you know how its possible to use "-" to subtract in the SELECT part of a query. So can you also use "+" to add? I've tried that and instead of adding the values together it does …

Read More