CODEWITHSUNDEEP
sqlms-accessms-access-2003
noname
noname

Reputation: 121

inserting multiple rows with 1 query

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?

Upvotes: 10

Views: 23413

Answers (5)

user3177671
user3177671

Reputation: 101

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

) ;

Upvotes: 0

Patrick Guimalan
Patrick Guimalan

Reputation: 1010

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

Upvotes: 0

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 4

SRIRAM
SRIRAM

Reputation: 1888

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

Upvotes: 1

Nipun Jain
Nipun Jain

Reputation: 601

insert into animal (animal_id, name, species)
values ( (1,'Foxi Maxi', 'dog'), (2, 'Dodo',' duck') , (3, 'Garfield', 'cat' ) )

use this example

Upvotes: -1

Related Questions