# 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](https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/table-output), [Table Input](https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/table-input), [Update](https://wiki.pentaho.com/display/EAI/Update), or [Delete](https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/delete-step-pdi).
* 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](https://773338310-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FYwnJ6Fexn4LZwKRHghPK%2Fuploads%2Fgit-blob-791558f99770370b647628bc70fcdfd03a52e25c%2FPDI_TransStep_Execute_SQL_Script_Dialog.png?alt=media)

### 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](https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/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](https://773338310-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FYwnJ6Fexn4LZwKRHghPK%2Fuploads%2Fgit-blob-15e3fde47bb332fc86678a019c6ddad87307800a%2FPDI_TransStep_Execute_SQL_Script_Example.png?alt=media)

The SQL script to execute might look like this:

```sql
CREATE TABLE tab?
(
  a INTEGER
);
```

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

{% hint style="info" %}
The transformation stops when a statement in the script fails.
{% endhint %}

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](https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/etl-metadata-injection) to pass metadata to your transformation at runtime.