Skip to main content
Hitachi Vantara Lumada and Pentaho Documentation

Execute SQL Script

You can execute SQL scripts with this step 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 that is sent to this step. 


  • Prepared SQL statements are not used due to the scripting and dynamic operation of this step, which can adversely affect transformation performance. If optimal performance is desired, then Pentaho recommends using dedicated steps like Table Output, Table Input, Update, or Delete
  • If the transformation halts unexpectedly, verify whether the Execute for each row? option is selected. For the SQL to start at the initialization phase of the transformation, ensure that Execute for each row? is not selected.


The following fields are general to this transformation step:

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 the name of a connected database from the list.
Edit (button) Click to edit your current database connection.
New (button) Click to establish a new database connection.
Wizard (button) Click to open a new database connection using the Wizard.



The Execute SQL Script step has the following options:

Option Description
SQL script to execute

Enter the SQL to execute. Separate statements with a semi-colon ( ; ) and use question marks as place holders for parameters.

The given parameters must be enclosed correctly. Numeric values do not need to be enclosed, but all others (for example, strings) must be enclosed with quotes ( ' ) or double quotes ( " ) depending on the database dialect.

The Bind parameters? and Quote Strings? options (explained below) are also allowed. 

Execute for each row?
  • (Default) This option executes the SQL statement at the step's initialization phase. 
  • Select this option to execute the SQL for each incoming row and to specify the optional parameters for records inserted, updated, deleted, or read (explained below). 
Execute as a single statement

Select this option to send the entire SQL statement to the database.

Leave this option unselected to split the statement semi-colons ( ; ). 

Variable substitution

Select this option to include the use of variables in the SQL (for example: ${table_name} ).

Bind parameters?

Select this option to bind parameters using prepared statements.

Leave this option unselected if you want this step to perform a string replacement of the parameters.

To use this option, the Execute for each row? option must also be selected.

Quote Strings?

Select this option to add quotes ( " ) around the string according to the database dialect and escape special characters like CR, LF, and " ". 

To use this option, the Execute for each row? option must also be selected.


There are two ways to populate the parameters:

  • Click Get Fields (explained below) to automatically populate these parameters with the same parameters specified in the transformation step previous to Execute SQL Script.
  • Specify the parameters that will replace the question marks in the query. 

The parameters must be presented in the same order as specified in the query. The first question mark will be replaced by the first parameter, the second question mark by the second parameter, and so on. 

If you need the same parameter multiple times in your query, then use multiple question marks.

Using a Select Values step, you can duplicate field values within the Select & Alter tab by selecting the value once and renaming it a second or third time. 

To use this option, the Execute for each row? option must also be selected.

Get Fields (button)

Click to automatically populate the Parameters in the Execute SQL Script with the same parameters that are specified in the transformation step previous to Execute SQL Script.

Optional Statistic Fields

Use these optional fields to collect statistics when the Execute for each row? parameter is selected. Each option will create a field in the data stream that contains the specific type of statistic.

Field Description
Field to contain insert stats Specify a field name to contain the statistic for the number of records that were inserted.
Field to contain Update stats Specify a field name contain the statistic for the number of records that were updated.
Field to contain Delete stats Specify a field name to contain the statistic for the number of records that were deleted.
Field to contain Read stats Specify a field name to contain the statistic for the number of records that were read.


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


The SQL script to execute might look like this:


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

The execution of the transformation will halt when a statement in the script fails. 

As an extra option, you can return the total number of inserts (INSERT INTO statements), updates (UPDATE table), deletes (DELETE FROM table) and reads (SELECT statements) by specifying the field names in the lower right of the dialog.

Metadata Injection Support

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