MS Access 中的多行插入语句

发布于 2024-09-14 18:22:20 字数 518 浏览 4 评论 0原文

我是一名 SQL 新手，试图弄清楚如何在一个 SQL 语句中插入多个表条目。这是我的代码：

INSERT INTO [Students](P_Id,FirstName,LastName,class,city,Phone)
SELECT 123,'Avi','Davis',2,'Tel-Mond','03-456789'
UNION
SELECT 234, 'Dani',2,'Dimona',' 02-111'
UNION 
SELECT 345,'Itzik',3,'Ariel', '03-2222'
UNION 
SELECT456, 'Koby', 3, 'Tel-Aviv', '03-333333'
UNION 
SELECT 789,'Moshe' ,2 , 'Tel-Aviv','03-7777777'

我已经尝试了各种教程中的“帮助”主题的各种变体，但到目前为止我尝试过的都不起作用。每个不同的数据库程序的 SQL 用法似乎略有不同。

关于如何更改我的代码以便它可以与 MS Access 一起使用，有什么建议吗？

原文

I'm an SQL newbie and trying to figure out how to insert multiple table entries in one SQL statement.
Here is my code:

INSERT INTO [Students](P_Id,FirstName,LastName,class,city,Phone)
SELECT 123,'Avi','Davis',2,'Tel-Mond','03-456789'
UNION
SELECT 234, 'Dani',2,'Dimona',' 02-111'
UNION 
SELECT 345,'Itzik',3,'Ariel', '03-2222'
UNION 
SELECT456, 'Koby', 3, 'Tel-Aviv', '03-333333'
UNION 
SELECT 789,'Moshe' ,2 , 'Tel-Aviv','03-7777777'

I've tried all sorts of variations on the theme with "help" from various tutorials, but nothing I've tried works so far. It seems like with each different DB program SQL usage differs slightly.

Any suggestions as to how to change my code so that it will work with MS Access?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

阳光下的泡沫是彩色的 2024-09-21 18:22:20

您可以在 MS Access 中执行类似的操作，但您必须有一个 from 表，并且必须注意仅返回一行：

INSERT INTO [Students](P_Id,FirstName, LastName, class,  city,Phone)
SELECT 123 As P_ID, 'Avi' As FirstName, 'Davis' As LastName, 2 As Class,
        'Tel-Mond' As City,'03-456789' As Phone FROM AnyTable
UNION
<...>

使用 VBA 和循环会更容易。

You can do something like that in MS Access, but you must have a from table, and you must take care that only one row is returned:

INSERT INTO [Students](P_Id,FirstName, LastName, class,  city,Phone)
SELECT 123 As P_ID, 'Avi' As FirstName, 'Davis' As LastName, 2 As Class,
        'Tel-Mond' As City,'03-456789' As Phone FROM AnyTable
UNION
<...>

It can be easier to use VBA and loop.

回复收藏 0 原文

七堇年 2024-09-21 18:22:20

我想扩展此处给出的答案，因为今天我尝试了多行插入方法以提高应用程序的性能，但没有得到任何明显的改进。

我通过使用类似于上面 Remou 的方法来实现它，但您可能想要 UNION ALL 否则不会插入两个相同的行，因为 UNION 有一个隐式的不同，我似乎需要一个别名的外部选择，否则它不起作用。另外，当您如上所述联合时，您需要 Access 中的 from 表，因此我遵循 Oracle 约定并创建了一个名为 DUAL 的单行表。

INSERT INTO [Students](P_Id,FirstName, LastName, class,  city,Phone)
SELECT * FROM
(
 SELECT 123 As P_ID, 'Avi' As FirstName, 'Davis' As LastName, 2 As Class,
        'Tel-Mond' As City,'03-456789' As Phone FROM DUAL
UNION ALL
 SELECT 456 As P_ID, 'FDA' As FirstName, 'RET' As LastName, 3 As Class,
        'lima' As City,'03-456789' As Phone FROM DUAL
.
.
.
.
) as MyAlias

在我的示例中，我使其变得更加简单，并创建了一个包含 varchar(50) 列的单列表。
我尝试了 1000 行，Access 抱怨“查询太复杂”。我必须将其减少到 49 行才能成功插入。这表明您需要将插入批量分成更小的块，以便 Access 接受它。

结果，性能并没有提高。这不值得麻烦，并且需要代码中的批处理逻辑，所以对我来说，这意味着我将在其他地方寻找性能提升。

I wanted to expand on the answers given here because today I tried the multi line insert approach in order to increase performance in our application and DID NOT get any noticable improvement.

I got it working by using an approach similar to Remou's above but you probably wanted UNION ALL otherwise two identical rows will not be inserted as UNION has an implicit distinct and I seemed to need an outer select that was aliased otherwise it didn't work. Additionally When you union as mentioned above you need a from table in Access so I followed the Oracle convention and created a single row table called DUAL.

INSERT INTO [Students](P_Id,FirstName, LastName, class,  city,Phone)
SELECT * FROM
(
 SELECT 123 As P_ID, 'Avi' As FirstName, 'Davis' As LastName, 2 As Class,
        'Tel-Mond' As City,'03-456789' As Phone FROM DUAL
UNION ALL
 SELECT 456 As P_ID, 'FDA' As FirstName, 'RET' As LastName, 3 As Class,
        'lima' As City,'03-456789' As Phone FROM DUAL
.
.
.
.
) as MyAlias

In my example I made it more simple and created a single column table with a varchar(50) column.
I tried this with 1000 rows and Access complained the "query is too complex". I had to take it down to 49 rows to get it to insert successfully. This suggests you would need to batch up your inserts into smaller chunks for Access to accept it.

As a result there was no increase in performance. It's not worth the hassle and requires the batch up logic in code so really for me means I will be looking elsewhere for performance gains.

回复收藏 0 原文