Sample Execute SQL for Results Column

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 for 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

The Example Execute SQL for Results Column workflow illustrates this technique.

Preparing the Workflow

To prepare the Example Execute SQL for Results Column workflow, drag its workflow template from the Templates folder into your own workspace. The workflow does not need to be modified.

Running the Workflow

To run the Example Execute SQL for Results Column workflow ‘Run Now’ from the context menu or press F5. The currently running workflow action is marked with an animated display. When the workflow execution has completed, examine the Execute SQL action’s Statements property. Note that the Results column in the source table is accessed by reference to the Execute SQL action’s String1 parameter, which stores the column name.