Running multiple statements

The Run SQL window allows you to run multiple SQL statements. Generally these would be Action statements, however the window can also be used to run multiple Select statements.

This feature is often used to run a series of SQL statements supplied in a file OR a series of generated statements.

SQL formatting

the statements may be on the same or different lines (line breaks are ignored).
the SQL statements are (by default) delimited by a semicolon. You can change this delimiter with Options > Run SQL > Statement Delimiter.
if your Statement Delimiter is Go, go at the start of a line is taken as the statement terminator. This option is often useful when running Sybase and SQL Server scripts.
if your Statement Delimiter is /, / at the start of a line is taken as a statement terminator. This option is often useful when running Oracle scripts.
two minus signs (--) appearing anywhere is taken as a line comment – all the text from there to the end of the line will be removed from your SQL (see Including comments in your SQL for more on this).
for Oracle any line starting with REM or @ will be ignored.
some databases allow comments to be placed between /* and */. AQT will not remove such comments from your SQL; these will be passed to your database along with your SQL. This is useful if you wish to use comments in Stored Procedure text.

Multiple statements

When you run a series of SQL statements, the Run Multiple SQL Statements window will be displayed. This will show you all the SQL statements in your script. Your statements are not run immediately, instead you must click on Run (or F5) to run them. This gives you the opportunity to:

review your SQL statements before running them
amend any statements if necessary
prevent any particular statement from running (by clicking on the Run? Column)
start the script at a particular statement
change some of the Run options
save your script if you have made changes to it

Auto-Commit

By default, AQT will operate in Auto-Commit mode. In this mode, every statement is committed after it has run. You can switch off Auto-Commit mode; AQT will then commit only when it hits a Commit / Rollback in your Script. Alternatively you can manually Commit / Rollback by clicking the Commit / Rollback buttons.

Stop on Error

This option specifies whether AQT is to stop processing the script if there is an error while processing an SQL statement. You have options:

Yes	Stops on any error
Yes, but ignore errors on Drops	Stops on any error, unless it occurs on a Drop statement. This option is available because scripts for building a database often consist of a number of Drop and Create statements. The Drops will fail if the objects do not already exist; this is an "expected" error and you may not want the script to stop running when this happens.
No	Do not stop on any errors
More	Displays the Error Codes dialog (see below).

Error Codes

AQT allows you to specify a number of error codes that are considered "normal" errors, so will not stop the script from running.

You do this on the Error Code window, which can be invoked from the More button, or View > Error code filter. In this window, you enter a number of SQLState codes, separated by commas. When an SQL statement gets this SQLState code, it will be regarded as a successful execution of the SQL.

The SQLState is a 5-character code given as the first part of all error messages. The following error has an SQLState of S0001:

S0001(-1303)[Microsoft][ODBC Microsoft Access Driver] Table 'Customer_Details' already exists.

Parameters

Your SQL statements may contain parameter markers. How AQT handles these is governed by the Always Prompt option.

when this option is checked (the default), AQT will prompt for parameters for every time it runs an SQL statements that contains parameters.
when this option is not checked, AQT will prompt for parameters only once. The values you supply will be taken as the parameters in subsequent SQL statements. This can be useful if you have multiple statements that have the same parameter values.

Other Buttons

Abort

Cancels the currently-running statement. This will only be active if you have specified Async Queries (See Options > Statement Options).

Pause

This stops the script after it has finished the currently-running query.

Skip

This button will be active only if your script has stopped because of an error. You can

either amend the statement then click on Run to re-run it
or click on Skip to skip this statement and continue from the next statement.

Multiple Select Statements

If your script has Select statements (or other statements that produce result-sets), by default a new display-results window will be created for each result-set.

If you want to have all the result-sets in the same window, use Options > Run SQL > Display Multiple Queries in same window. When you are in this mode:

all results produced by the script go to the same window (eg. are appended one after another). If you specify Options > Run SQL > Include a blank line betweeen query results, then AQT will write a blank line between the results from the different queries.
if the script stops (either with an error, or you hit Pause) then, when you restart by clicking on Run, AQT will continue appending the data to the window.
If you click on Select > All (which is a way to rerun the script from the start), AQT will write the results to the start of a new window.

When you are displaying multiple results-sets in the same display window, you will not be able to use the Row update/delete/insert functions.

Export Data

You can use Export Data when running multiple SQL statements. In the Run SQL window this is done by F7 or Export Data to > File. When running in this mode, the following rules apply:

You will not be prompted for the export options right away, instead this will be done when AQT runs the first SQL statement that produces a result-set.
All subsequent result-sets will go the same file (with the same export options) except that the file mode will be changed to Append. In other words, all the data will be appended to the same file. Excel works slightly differently (see next point).
For Excel, if you specify a Worksheet name, all the result-sets will be appended to the same Worksheet. If you do not specify a Worksheet name, every result-set will go to a different Worksheet.
If the script stops (either with an error, or you hit Pause) then, when you restart by clicking on Run, AQT will continue writing to the file in Append mode.
If you click on Select > All (which is a way to rerun the script from the start), AQT will reset the export options. You will be re-prompted for them.
If you do not want all your output to go to the same file, you can code export specifications in your script to change the export options in the middle of your script.
If you want your output to go to different files, you can use the keywords <schema> and <table> in your file names. When the export is run, <schema> and <table> are replaced with the schema and table-name of the table you are exporting. <schema> and <table> can also be used in the Worksheet name (if exporting to Excel), or insert_tab (if exporting as insert statements).

Viewing only some Statements

You can use the View menu option to view only some of your SQL statements, eg:

only those that have failed
only those that have suceeded
only those that have not yet run
only those that have updated / deleted zero rows

These options can be useful if you have many SQL statements in your script, and you only wish to view particular statements.

Amending the Script

You can amend the statements in the script. You can do this by either amending the statement text in the SQL box, or double-clicking it for a larger editing window.

Having amended the script, you can save your changes:

use Edit > Copy script back to Run SQL window to update the script in the SQL window with the changes you have made.
use Edit > Copy script to file to save the script to a file.

When generating the script, you may wish to have a blank line placed between the statements. Select option Edit > When copying, include blank line between statements if you wish to do this.

When you copy the script, AQT will only copy the rows that are currently displayed. For instance, if you have selected View > Show statements that suceeded, it will only copy the statements that were successful.