有什么方法可以在ms-access查询中创建多个insert语句? [英] Is there any way to create multiple insert statements in a ms-access query?

问题描述

我正在使用MS Access2003.我想在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)，以欺骗访问以允许并集(它必须至少包含一行)，并且您需要"top 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查询中创建多个insert语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！