inserting multiple rows with 1 query

Question

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?

Solution 1

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.

Solution 2

try this

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

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

refer here

SQL code to insert multiple rows in ms-access table

Comments

[noname]

i use this method, but there still appear a pop up "missing semicolon(;) at the end of SQL statement

[SRIRAM]

add a semicolon at last @FenediW