使用1个查询插入多行

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

我在使用ms access 2003插入多行和1个查询时遇到问题。当我像下面的代码一样使用INSERT INTO时

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?

问题,ms访问总是会出现在SQL语句结束后找到的弹出字符。那么,有没有办法将数据插入表中?

5 个解决方案

#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.

使用Access SQL,您无法组合两个INSERT语句。你可以分别运行它们。但是如果您需要使用单个语句来执行此操作,则需要使用更复杂的查询。

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.

Dual是一个自定义表,设计为始终只包含一行。您可以使用此Stack Overflow应答中的说明创建自己的Dual表。

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.

但是,实际上并不需要用于此目的的自定义表。您可以使用任何只返回一行的表或查询,而不是Dual。

#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

用于在ms-access表中插入多行的SQL代码

#3

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

不幸的是,它看起来MS Access不兼容,只允许单个查询插入,除非你有一个源表。如果您可以将其挂钩到可以执行查询的工具,那么您可以创建一个循环来运行多个插入

#4

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

) ;

#5

-1

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

use this example

使用这个例子

#1