You can insert, delete, update, and retrieve rows from a database using SQL transformation midstream in a (Mapping)pipeline.
The SQL transformation runs in two mods
1) Script mode
2) Query mode
Lets see how to use script mode in detail.
Script mode can be used for Delete , Insert and Update data in the table. Create Table script.
Lets see the steps:-
Create a file with the scripts like below and name the file (ex:- Create_script)
Create a another file which will have the path and name of the above script and this file will act as our source file in the mapping
Steps of the mapping:-
Create source Flat file with the a column (ex:- FILE_NAME)
Create Target Flat File with 2 columns for Result and Error
Create SQL Transformation for Script mode.
And use the SCRIPT_NAME.txt as source file(don’t use the actual script as your source).
Follow the above screen shot to select SQL transformation.
And then select the script mode radio button as shown above. Also select the data base where you want to create the table (in our example we are creating 2 tables in Oracle DB).
** when you select Script mode by default (SQL transformation will run in passive check box will be checked and disabled).
The final mapping will look like below.
Run the session and then check if the tables PERSONS AND ORDERS are created in Oracle DB.
before that lets see if those tables are present in our DB.