Execute SQL Actions
Execute SQL actions apply a set of SQLite SQL statements to the contents of one or more Table datasets. The results are stored in a Table dataset.
| Action Property | Description |
|---|---|
| Options | A String List parameter which determines how the Source table is copied to the Result table before the SQL statements are applied. |
| Result | A Table dataset which stores the results of the Execute SQL action. |
| Source1 | A Table dataset providing source data for the Execute SQL action. |
| Source2 | A second Table dataset providing source data for the Execute SQL action (optional). |
| Statements | A String List parameter containing a list of SQL statements to be executed. |
| String1 | A String parameter or dataset containing a value that may be substituted into the SQL statements to be executed (optional). |
| String2 | A second String parameter or dataset containing a value that may be substituted into the SQL statements to be executed (optional). |
‘Options’ Action Property
The ‘Options’ action property is a String List parameter which determines how the Source table is copied to the Result table before the SQL statements are applied. The String List parameter may include any of the following values:
MakeTable Source1 Result
An empty table is created in the dataset referred to by the ‘Result’ action property. This table has the same field definitions as the Table dataset referred to by the ‘Source1’ action property.
MakeTable Source2 Result
An empty table is created in the dataset referred to by the ‘Result’ action property. This table has the same field definitions as the Table dataset referred to by the ‘Source2’ action property.
CopyRows Source1 Result
The rows in the Table dataset referred to by the ‘Source1’ action property are copied to the Table dataset referred to by the ‘Result’ action property.
CopyRows Source2 Result
The rows in the Table dataset referred to by the ‘Source2’ action property are copied to the Table dataset referred to by the ‘Result’ action property.
NoDataCopyRows Source1 Source2 Result
If the ‘Source1’ table contains no rows, the rows in the ‘Source2’ table are copied to the ‘Result’ table. The ‘Source1’ and ‘Source2’ tables should normally have the same column definitions. If a NoDataCopyRows statement is used, a CopyRows statement should normally also be specified in order to copy any rows that exist in the first Source table.
None or <Blank>
No tables are copied to or created in the Result dataset.
Statements Action Property
The Statements action property is a String List parameter which contains a list of SQL statements to be executed. SQL statements may refer to the Result, Source1, Source2, String1 and String2 action properties by name. Each item in the String List may contain a single SQL statement, or two or more SQL statements separated by semicolons (;).
The following may also be used in the ‘Statements’ parameter to set one or more key fields in the ‘Result’ table.
SET RESULT TABLE KEYS (<Field1>[, <FieldN>])
See the Compare Tables action for further details.
Keyword Substitution
The SQL statements in an Execute SQL action use the keywords Source1, Source2 and Result to represent the action’s input and output datasets. When an Execute SQL action is run, these keywords are replaced by the internal table names used by the execution engine.
This can cause a problem if your SQL statements use a keyword in a context that is not a reference to the action parameters. For example, the following SQL statement might be used to calculate the sum of a column named Results in the Source1 table.
INSERT INTO Result SELECT sum(Results) from Source1
At runtime, the ‘Result’ portion of the ‘Results’ string will be substituted with the internal table name that refers to the Result table, causing an SQL error. To resolve this problem, store the Results column name in the Execute SQL action’s String1 parameter, and modify the SQL statement so it looks like this:
INSERT INTO Result SELECT sum(String1) from Source1
See the ‘Sample Execute SQL for Results Column’ workflow in the Tools templates folder for an example of this technique.
Example:
To examine the details for an Execute SQL action, double-click the action node, or select ‘View Details’ from the node’s context menu. The Details screen displays the number of records read from the Source table, the number of records written to the Result table and the time taken for the action to complete.
The progress of an Execute 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 SQL action has been running. |
| Estimated total time (seconds) | The estimated total time for which the Execute SQL action will run. |
| Remaining time (seconds) | The estimated remaining time for which the Execute SQL action will run. |
| Remaining time (hours/minutes) | The estimated remaining time (in hours or minutes) for which the Execute SQL action will run. |
| Total elapsed time hh:mm:ss | The time for which the Execute SQL action has been running in hours, minutes and seconds. |
Press the F5 key periodically to update the display.
Execute SQL actions support the use of the Define Action Parameters Wizard, which allows additional ‘Source’, ‘String’ or ‘Result’ action properties to be created.
SQLite Resources
The SQLite home page includes a number of different SQLite resources, and may be found at http://www.sqlite.org.