有没有办法在ms-access查询中创建多个insert语句？

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?

我正在使用MS Access 2003.我想在MS Access中的“查询”中运行大量插入SQL语句。有没有简单的(或者确实是任何方式)做到这一点?

7 个解决方案

#1

yes and no.

是的,不。

You can't do:

你做不到:

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

but you can do

但你可以做到

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

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.

如果您还没有表格中的数据,那会给您带来什么?那么,你可以制作一个Select语句,它由许多Selects联合组成,带有硬编码结果。

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)

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

注意:我还必须包含某种形式的虚拟表(例如,onerow)来欺骗访问以允许联合(它必须至少有一行),并且您需要“前1”以确保您不要对于包含多行的表格,我会重复一遍

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

但话说回来,只做三个单独的插入语句可能会更容易,特别是如果你已经在循环中构建了东西(当然除非你的插入成本高于编码时间的成本) 。

#2

Personally, I'd create a VBA subroutine to do it, and connect to the database using some form of sql connection.

就个人而言,我创建了一个VBA子例程来执行它,并使用某种形式的sql连接连接到数据库。

Off the top of my head, the code to do it should look something like:

在我的头顶,执行它的代码应该类似于:

Sub InsertLots ()
    Dim SqlConn as Connection
    SqlConn.Connect("your connection string")
    SqlConn.Execute("INSERT <tablename> (column1, column2) VALUES (1, 2)")
    SqlConn.Execute("INSERT <tablename> (column1, column2) VALUES (2, 3)")
    SqlConn.Close()
End Sub

#3

I think it's inadvisable to propose a particular data interface, as Jonathan does, when you haven't clarified the context in which the code is going to run.

我认为建议一个特定的数据接口是不可取的,正如Jonathan所做的那样,当你还没有明确代码将要运行的上下文时。

If the data store is a Jet database, it makes little sense to use any form of ADO unless you're running your code from a scripting platform where it's the preferred choice. If you're in Access, this is definitely not the case, and DAO is the preferred interface.

如果数据存储是Jet数据库,那么使用任何形式的ADO都没有意义,除非您从脚本平台运行代码,而这是首选的选择。如果你在Access中,肯定不是这种情况,DAO是首选的界面。

#4

No - a query in Access is a single SQL statement. There is no way of creating a batch of several statements within one query object. You could create multiple query objects and run them from a macro/module.

否 - Access中的查询是单个SQL语句。无法在一个查询对象中创建一批多个语句。您可以创建多个查询对象并从宏/模块运行它们。

#5

@Rik Garner: Not sure what you mean by 'batch' but the

@Rik Garner:不确定'批处理'是什么意思,但是

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)

construct, although being a single SQL statement, will actually insert each row one at a time (rather than all at once) but in the same transaction: you can test this by adding a relevant constraint e.g.

构造虽然是一个单独的SQL语句,但实际上每次只插入一行(而不是一次全部),但是在同一个事务中:你可以通过添加一个相关的约束来测试它。

ALTER TABLE foo ADD
   CONSTRAINT max_two_foo_rows
      CHECK (2 >= (SELECT COUNT(*) FROM foo AS T2));

Assuming the table is empty, the above INSERT INTO..SELECT.. should work: the fact it doesn't is because the constraint was checked after the first row was inserted rather than the after all three were inserted (a violation of ANSI SQL-92 but that's MS Access for you ); the fact the table remains empty shows that the internal transaction was rolled back.

假设表是空的,上面的INSERT INTO..SELECT ..应该可以工作:事实并非如此,因为在插入第一行之后检查了约束而不是在插入所有三个之后(违反ANSI SQL) -92但这是你的MS Access);表保持为空的事实表明内部事务已回滚。

@David W. Fenton: you may have a strong personal preference for DAO but please do not be too hard on someone for choosing an alternative data access technology (in this case ADO), especially for a vanilla INSERT and when they qualify their comments with, " Off the top of my head, the code to do it should look something like…" After all, you can't use DAO to create a CHECK constraint :)

@David W. Fenton:您可能对DAO有强烈的个人偏好,但请不要选择替代数据访问技术(在本例中为ADO),特别是对于一个香草INSERT,以及当他们使用,“脱离我的头脑,做它的代码应该看起来像......”毕竟,你不能使用DAO来创建CHECK约束:)

#6

MS Access does not allow multiple insert from same sql window. If you want to insert, say 10 rows in table, say movie (mid, mname, mdirector,....), you would need to open the sql windows,

MS Access不允许从同一个sql窗口进行多次插入。如果你想在表格中插入10行,比如说电影(mid,mname,mdirector,....),你需要打开sql窗口,

type the 1st stmt, execute 1st stmt, delete 1st stmt

输入第1个stmt,执行第1个stmt,删除第1个stmt

type the 2nd stmt, execute 2nd stmt, delete 2nd stmt

键入第二个stmt,执行第二个stmt,删除第二个stmt

type the 3rd stmt, execute 3rd stmt, delete 3rd stmt ......

键入第3个stmt,执行第3个stmt,删除第3个stmt ......

Very boring. Instead you could import the lines from excel by doing:

很无聊。相反,您可以通过执行以下操作从excel导入行:

Right-click on the table name that you have already created

右键单击已创建的表名

Import from Excel (Import dialog box is opened)

从Excel导入(打开导入对话框)

Browse to the excel file containing the records to be imported in the table

浏览到包含要在表中导入的记录的excel文件

Click on "Append a copy of the records to the table:"

单击“将记录副本附加到表中:”

Select the required table (in this example movie)

选择所需的表格(在此示例中为电影)

Click on "OK"

点击“确定”

Select the worksheet that contains the data in the spreadsheet

选择包含电子表格中数据的工作表

Click on Finish

单击Finish

The whole dataset in the excel has been loaded in the table "MOVIE"

excel中的整个数据集已加载到“MOVIE”表中

#7

MS Access can also Append data into a table from a simple text file. CSV the values (I simply used the Replace All box to delete all but the commas) and under External Data select the Text File.

MS Access还可以从简单的文本文件将数据附加到表中。 CSV值(我只使用“全部替换”框删除除逗号之外的所有内容)并在“外部数据”下选择“文本文件”。

From this:
INSERT INTO CLASS VALUES('10012','ACCT-211','1','MWF 8:00-8:50 a.m.','BUS311','105');
INSERT INTO CLASS VALUES('10013','ACCT-211','2','MWF 9:00-9:50 a.m.','BUS200','105');
INSERT INTO CLASS VALUES('10014','ACCT-211','3','TTh 2:30-3:45 p.m.','BUS252','342');
To this:
10012,ACCT-211,1,MWF 8:00-8:50 a.m.,BUS311,105
10013,ACCT-211,2,MWF 9:00-9:50 a.m.,BUS200,105
10014,ACCT-211,3,TTh 2:30-3:45 p.m.,BUS252,342

#1