Execute SQL Script

Use the Execute SQL Script step to execute SQL scripts using either of the following methods:

• Execute the SQL script once during the initialization phase of the transformation.

• Execute the SQL script once for every input row sent to the step.

Notes

• Prepared SQL statements are not used due to the scripting and dynamic operation of this step, which can adversely affect transformation performance. For better performance, use dedicated steps like Table Output, Table Input, Update, or Delete.

• If the transformation stops unexpectedly, verify whether Execute for each row? is selected. To run the SQL during transformation initialization, clear Execute for each row?.

General

Field	Description
Step name	Specify the unique name of the Execute SQL Script step on the canvas. You can customize the name or leave it as the default.
Connection	Select a database connection.
Edit	Edit your current database connection.
New	Create a new database connection.
Wizard	Create a new database connection using the Database Explorer.

Execute SQL Script dialog

Options

Option	Description
SQL script to execute	Enter the SQL to execute. Separate statements with a semicolon (;). Use question marks (?) as placeholders for parameters.

For parameters, numeric values do not require quotes, but string values must be enclosed with single quotes (') or double quotes ("), depending on the database dialect.

The Bind parameters? and Quote Strings? options also apply. | | Execute for each row? | If cleared (default), executes the SQL during step initialization.

If selected, executes the SQL for each incoming row and enables parameter and statistic field options. | | Execute as a single statement | If selected, sends the entire SQL string to the database as a single statement.

If cleared, splits the SQL at semicolons (;). | | Variable substitution | Enables variables in the SQL, such as ${table_name}. | | Bind parameters? | Binds parameters using prepared statements.

If cleared, the step performs string replacement.

Note: To use this option, Execute for each row? must be selected. | | Quote Strings? | Adds quotes around strings according to the database dialect and escapes special characters like carriage return (CR), line feed (LF), and quotes.

Note: To use this option, Execute for each row? must be selected. | | Parameters | You can populate parameters in two ways:

• Select Get Fields to populate parameters from the previous step.

• Manually specify parameters that replace the question marks (?) in the SQL.

Parameters must be in the same order as the question marks in the SQL. If you need the same parameter multiple times, use multiple question marks.

You can use Select Values to duplicate fields by selecting a field once and renaming it multiple times.

Note: To use parameters, Execute for each row? must be selected. | | Get Fields | Populates Parameters using the fields from the previous step. |

Optional statistic fields

Use these optional fields to collect statistics when Execute for each row? is selected. Each option creates a field in the data stream that contains the specified statistic.

Field	Description
Field to contain insert stats	Field that contains the number of records inserted.
Field to contain update stats	Field that contains the number of records updated.
Field to contain delete stats	Field that contains the number of records deleted.
Field to contain read stats	Field that contains the number of records read.

Example

If you want to create five tables (tab1, tab2, tab3, tab4, and tab5), you could create a transformation similar to this one:

Execute SQL Script example

The SQL script to execute might look like this:

CREATE TABLE tab?
(
  a INTEGER
);

The field name to specify as the parameter is the count sequence defined in the second step.

The transformation stops when a statement in the script fails.

As an additional option, you can return the total number of inserts (INSERT INTO), updates (UPDATE), deletes (DELETE FROM), and reads (SELECT) by specifying statistic field names.

Metadata injection support

All fields in this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.