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?
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.
try this
INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )
INSERT INTO Employee values (('1','b','c'),('2','d','e'));
refer here
Unfortunately, it appears MS Access is not compatible and only allows single query insert unless you have a source table. If you can hook this to a tool that can execute the query, then you can make a loop to run the multiple insert
Use this confirm working query:
INSERT INTO Product (Code,Name,IsActive,CreatedById,CreatedDate )
SELECT * FROM (
SELECT '10001000' AS Code,'Blackburn sunglasses' AS Name,1 AS IsActive,1 AS CreatedById,'2/20/2015 12:23:00 AM' AS CreatedDate FROM Product
UNION SELECT '10005200' AS Code,'30 panel football' AS Name,1 AS IsActive,1 AS CreatedById,'2/20/2015 12:23:09 AM' AS CreatedDate FROM Product
) ;
insert into animal (animal_id, name, species)
values ( (1,'Foxi Maxi', 'dog'), (2, 'Dodo',' duck') , (3, 'Garfield', 'cat' ) )
use this example
