Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features

Buy now

Download now

SQL*XL online help


SQL*XL: Batch Execite Multiple Statements

Batch Execute Multiple Statements


The SQL dialog in SQL*XL has the capability to take multiple SQL statements and other commands at the same time. When submitting more than 1 SQL statement each statement should be ended with a semi colon character (;). The source of the SQL does not matter. Multiple select statements can be submitted from a SQL file, a worksheet or from the SQL dialog itself. Let's give a few examples:

To query both the emp and the dept table in one go enter the following into the SQL dialog (note that it also times these queries):

set timing on;
select * from emp;
select * from dept;

Clicking the OK button on the SQL dialog both statements will be processed and the Resultset Options dialog will be shown for each statement. In the Resultset Options dialog you can specify where to put the results of the query. Click OK on the dialog to continue with the first statement. The same Resultset Options dialog is shown but now for the second query. Note that the field "Paste results in cell" is automatically set to 2 rows below the resultset of the first query. Click OK to accept the default and there will be 2 resultsets in the active worksheet.

In order to provide a fully flexible system, at each stage in this process of executing multiple queries you can decide to skip the Resultset dialog. There are 4 buttons with which to influence the behavior of the Resultset dialog:

1- OK button:
The OK button will execute the current query and show the Resultset dialog for the next query. Use this button to change the paste options for each query.

2- OK to all button:
This button will execute the current query. All subsequent queries will use the same options except for the start cell where the pasting of the resultset will commence. The start cell is calculated automatically. SQL*XL will skip 1 line by default before pasting a new resultset. This default behavior can be customised. In the SQL*XL settings file, in the General section, you find options how many rows and columns to skip between 2 select statements by default.

3- Skip button:
This button will discard the present SQL statement and continue with the next statement. The Resultset Options dialog will be shown for the next select statement. This button can be used e.g. when too many rows are fetched. This figure is optionally displayed on the Resultset options dialog.

4- Skip all button:
This button effectively cancels the whole operation. The current SQL statement will be canceled as well as all other pending statements.

When the user is sure the default settings on the Resultset dialog are ok, on the SQL dialog it can be specified to skip the Resultset dialog altogether. Use the "Prompt to specify options" list and select the "Never" item to not be displayed the Resultset options dialog.

Remember that in all SQL*XL dialogs the default settings can be altered in the preferences file.

See also:

SQL*XL ribbon in Excel