In the previous paragraphs, you learned how to use the basic steps to perform CRUD--Create, Read, Update, and Delete operations. For simple use cases, these steps should be enough. There are, however, some situations where you may need a bit more flexibility. For example, suppose that you want to insert rows under one of the following conditions:
WHERE upper(column1) > column2 OR column1 is null
This looks like a very common statement but the Insert step doesn't allow us to enter such a condition.
This and other similar situations can be implemented with the Execute row SQL script step. To use this step, you just create a new string field and use it to define the SQL statement to execute. After that, you add an Execute row SQL script step and the step will execute for every input row. Alternatively, there is another step, the Execute SQL script step. If you check its Execute for each row? checkbox, the behavior is similar. Compared to the previous step, this one allows you to include parameters in your statements.
The Execute SQL script step has another useful purpose. With the Execute for each row? option unchecked, it can be dropped alone in the work area--that is, unlinked from any other step. The statements that you write in its configuration window will execute once during the initialization phase of the Transformation. Common uses for this is to clear status and flags in configuration tables, delete records from tables that will be loaded in the current Transformation, among others.