有没有办法在 ms-access 查询中创建多个插入语句? [英] Is there any way to create multiple insert statements in a ms-access query?

问题描述

我正在使用 MS Access 2003.我想在 MS Access 中所谓的查询"中运行大量插入 SQL 语句.有什么简单的(或确实有任何方法)可以做到吗?

I am using MS Access 2003. I want to run a lot of insert SQL statements in what is called 'Query' in MS Access. Is there any easy(or indeed any way) to do it?

推荐答案

是和否.

你不能这样做:

insert into foo (c1, c2, c3)
values ("v1a", "v2a", "v3a"),
       ("v1b", "v2b", "v3b"),
       ("v1c", "v2c", "v3c")

但是你可以做到

insert into foo (c1, c2, c3)
    select (v1, v2, v3) from bar

如果您还没有表格中的数据，这对您有什么好处?好吧，您可以制作一个 Select 语句，该语句由许多带有硬编码结果的 Select 联合组成.

What does that get you if you don't already have the data in a table? Well, you could craft a Select statement composed of a lot of unions of Selects with hard coded results.

INSERT INTO foo (f1, f2, f3)
    SELECT *
    FROM (select top 1 "b1a" AS f1, "b2a" AS f2, "b3a" AS f3 from onerow
    union all
    select top 1 "b1b" AS f1, "b2b" AS f2, "b3b" AS f3 from onerow
    union all 
    select top 1 "b1c" AS f1, "b2c" AS f2, "b3c" AS f3 from onerow)

注意:我还必须包含某种形式的虚拟表(例如，onerow)以欺骗访问以允许联合(其中必须至少有一行)，并且您需要前 1"确保您不会重复一行以上的表格

Note: I also have to include a some form of a dummy table (e.g., onerow) to fool access into allowing the union (it must have at least one row in it), and you need the "top 1" to ensure you don't get repeats for a table with more than one row

但话说回来，只执行三个单独的插入语句可能会更容易，尤其是如果您已经在循环中构建事物(当然，除非进行插入的成本大于编码时间的成本).

But then again, it would probably be easier just to do three separate insert statements, especially if you are already building things up in a loop (unless of course the cost of doing the inserts is greater than the cost of your time to code it).

这篇关于有没有办法在 ms-access 查询中创建多个插入语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！