A dynamic SQL query can execute different query statements for each input row. When you create a dynamic SQL query, you use string substitution to define string variables in the query and link them to input fields in the transformation.
To change a query statement, configure a string variable in the query for the portion of the query that you want to change. To configure the string variable, identify an input field by name in the query and enclose the name in tilde characters (
). The query changes based on the value of the data in the field.
The transformation input field that contains the query variable must be a string data type. You can use string substitution to change the query statement and the query data.
When you create a dynamic SQL query,
prepares a query for each input row. You can pass the following types of dynamic queries in an input field:
You can substitute the entire SQL query with query statements from source data.
You can substitute a portion of the query statement, such as the table name.