Execute External SQL Actions
Execute External SQL actions allow tables to be read from external databases and stored in Table datasets. The action also allows the contents of Table parameters or datasets to be written to an external database, either as new tables or as rows appended to existing database tables. The connection details for the action’s external database are defined in an External Data Source.
| Action Property | Description |
|---|---|
|
External Data Source |
An External Data Source parameter or dataset which provides connection details for the external database to be read from or written to. SQL Server, SQLite, Access, DB2 and Custom External Data Sources are supported. Although these External Data Source definitions require a specific table name, the action is able to access all tables in the external database. |
|
Result |
A Table dataset which stores table rows read from the external database. |
|
Source1 |
A Table parameter or dataset to be written to the external database or appended to an existing database table. |
|
Source2 |
A second Table parameter or dataset to be written to the external database or appended to an existing database table (optional). |
|
Statements |
A String List parameter containing a list of statements to be executed. This may include general-purpose SQL statements and statements specific to the Execute External SQL action, as described below. |
|
String1 |
A String parameter or dataset containing a value that may be substituted into the statements to be executed (optional). |
|
String2 |
A second String parameter or dataset containing a value that may be substituted into the statements to be executed (optional). |
‘Statements’ Action Property
The ‘Statements’ action property is a String List parameter which contains a list of statements to be executed in sequence. These may include SQL statements supported by the external database, and any of the following statements, which are specific to the Execute External SQL action.
The statements may refer to the ‘Source1’, ‘Source2’, ‘String1’, ‘String2’ and ‘Result’ action properties by name. Each statement should be stored as a separate string entry in the ‘Statements’ string list parameter.
ImportTable <table name> Result
This statement copies the <table name> table from the external database to the Result dataset.
ExportTable Source1 <table name>
This statement copies the Source1 table to the <table name> table in the external database. If the <table name> table already exists in the external database, it is removed and then recreated using the schema from the Source1 table. For DB2 External Data Sources, the external table name should be specified using UPPER CASE.
ExportTable statements are not supported for Custom External Data Sources.
AppendRows Source1 <table name>
This statement inserts rows from the Source1 table into an existing table named <table name> in the external database. The external database may generate an error if the Source1 and <table name> tables have different column definitions.
ImportSelect Result <SELECT statement>
This statement copies the rows returned by the specified SELECT statement into the Result dataset. The SELECT statement may refer to any of the tables defined in the specified External Data Source.
Examples:
ImportTable/ImportSelect:
ExportTable/AppendRows:
LiveCompare Data Types
LiveCompare uses only INT, FLOAT and NVARCHAR data types when creating Table datasets. Importing from or exporting to tables that use other data types may cause unexpected results.
To examine the details for an Execute External SQL action, double-click the action node, or select ‘View Details’ from the node’s context menu. The Details screen displays the number of statements executed, the number of records written to the Result table and the time taken for the action to complete.
The progress of an Execute External SQL action may be monitored by viewing the Details screen while the action is running. Time estimates are available after a sufficient number of statements have been processed, relative to the total number of statements. The following details are shown:
| Item | Description |
|---|---|
|
Total objects |
The total number of statements to be processed. |
|
Remaining objects |
The number of statements that have yet to be processed. |
|
Elapsed time (seconds) |
The time for which the Execute External SQL action has been running. |
|
Estimated total time (seconds) |
The estimated total time for which the Execute External SQL action will run. |
|
Remaining time (seconds) |
The estimated remaining time for which the Execute External SQL action will run. |
|
Remaining time (hours/minutes) |
The estimated remaining time (in hours or minutes) for which the Execute External SQL action will run. |
|
Total elapsed time hh:mm:ss |
The time for which the Execute External SQL action has been running in hours, minutes and seconds. |
Press the F5 key periodically to update the display.
Execute External SQL actions support the use of the Define Action Parameters Wizard which allows additional ‘Source’, ‘String’ or ‘Result’ action properties to be created.