在一次操作中运行多条 SQL 语句

Running multiple SQL statements in the one operation

提问by Smashery

I'm trying to use ADO to create several tables at once, into MS Access. Is it possible to do multiple statements in the one operation? For instance:

我正在尝试使用 ADO 一次创建多个表到 MS Access 中。是否可以在一个操作中执行多个语句？例如：

...
// I have omitted the field details 
CString sQuery = "CREATE TABLE [Table1] (..., PRIMARY KEY ([ID])); \nCREATE TABLE [Table2] (..., PRIMARY KEY ([ID]));";
oRecordset.Open(oDatabase.m_pConnection, sQuery)

This fails due to a "Syntax Error in CREATE TABLE statement", although each of the create statements work on their own perfectly. Is there a way of doing this sort of thing? There will also be statements to add constraints, add indexing, etc., and I'd really like to be able to do it so that I don't have to split up the string into separate parts.

这由于 a 失败"Syntax Error in CREATE TABLE statement"，尽管每个 create 语句都可以完美地工作。有没有办法做这种事情？还会有语句来添加约束、添加索引等，我真的很想能够这样做，这样我就不必将字符串分成单独的部分。

采纳答案by Jeremy

ADO to MS Access does not support batch SQL statements. You need to run each statement as a separate execution.

ADO to MS Access 不支持批处理 SQL 语句。您需要将每个语句作为单独的执行来运行。

回答by onedaywhen

ADO isn't the issue: the ACE/Jet engine simply does not support multiple SQL statements within a single operation. In other words, ACE/JET SQL lacks procedural syntax found in most 'industrial-strength' SQL products. See @David-W-Fenton's answer for more detail.

ADO 不是问题：ACE/Jet 引擎根本不支持单个操作中的多个 SQL 语句。换句话说，ACE/JET SQL 缺少大多数“工业强度”SQL 产品中的过程语法。有关更多详细信息，请参阅 @David-W-Fenton 的回答。

Bottom line: You will need to issue a Connection.Executefor each CREATE TABLEstatement i.e. client side procedural code. But they can (perhaps should) all be run in the same transaction, of course.

底线：您需要Connection.Execute为每个CREATE TABLE语句发出一个，即客户端程序代码。但是，当然，它们可以（也许应该）都在同一个事务中运行。

回答by David-W-Fenton

People who think you can send multiple SQL statements to Jet in a batch just aren't thinking.

认为您可以批量向 Jet 发送多个 SQL 语句的人并没有想到。

Jet is a file-server database engine -- there is no centralized server process controlling interaction between clients and the actual data store. Instead, clients are all running individual instances of Jet and cooperatively editing a file in a way that is controlled by the Jet locking file (LDB). Without a centralized process to serialize and prioritize the SQL statements, you wouldn't wantJet to be able to process multiple statements in a batch.

Jet 是一个文件服务器数据库引擎——没有中央服务器进程控制客户端和实际数据存储之间的交互。相反，客户端都在运行 Jet 的各个实例，并以 Jet 锁定文件 (LDB) 控制的方式协作编辑文件。如果没有集中处理 SQL 语句的序列化和优先级，您不会希望Jet 能够批量处理多个语句。

Those who are offering the suggestion of using ADO and separating the statements with a CrLf should code it up and give it a try and then get back to us about how useful their speculative advice actually is.

那些提供使用 ADO 并用 CrLf 分隔语句的建议的人应该对其进行编码并尝试一下，然后再告诉我们他们的推测性建议实际上有多大用处。

回答by oldbaritone

Crude but it works - create the necessary number of queries with one SQL statement each, then use a Macro to run the queries successively. That's about as good as can be done with ADO/Jet.

粗略但它有效 - 使用每个 SQL 语句创建必要数量的查询，然后使用宏连续运行查询。这与使用 ADO/Jet 所能做到的一样好。

回答by dkretz

If you're sample set of commands is typical, just do something like this in VBA or the language of your choice:

如果您的示例命令集是典型的，只需在 VBA 或您选择的语言中执行以下操作：

public sub ExeuteBatch(BatchString as String)
    var s as string  
    var abatch as array  
    sbatch = replace(sbatch, "\n", "")
    abatch = split(BatchString, ";")
    for each s in abatch
        ** adodb execute s here **
    next s
end sub

public sub ExeuteBatch(BatchString as String)
    var s as string  
    var abatch as array  
    sbatch = replace(sbatch, "\n", "")
    abatch = split(BatchString, ";")
    for each s in abatch
        ** adodb execute s here **
    next s
end sub

That's off the top of my head, but you should be able to take it from there I hope.

这是我的头顶，但我希望你应该能够从那里拿走它。

回答by GordyII

I think you can run multiple commands in one ADO Command.

我认为您可以在一个 ADO 命令中运行多个命令。

You just need proper line feeds between then. i.e. \n doesn't work.

你只需要适当的换行符。即 \n 不起作用。

Try something like this: (Using VB Syntaxish)

尝试这样的事情：（使用 VB 语法）

MyQuery = "Select * from Whatever " & vbLf <br>
MyQuery = MyString & "Select * from SomethingElse " & vbLF

oRecordset.Open(oDatabase.m_pConnection, MyQuery )

回答by Jhonny D. Cano -Leftware-

I don't know if ADO is constructed over JET OleDB Engine, which I suppose, if it is this way, The Jet Engine doesn't support execution of multiple statements in one single batch, we tryed separating with ; and with the GO reserved word, but it does not work.

我不知道 ADO 是否是在 JET OleDB 引擎上构建的，我想，如果是这样，Jet 引擎不支持在一个批处理中执行多个语句，我们尝试用 ; 并使用 GO 保留字，但它不起作用。