Performing Insert Operations Using a SQL Transformation Project

Create a sample SQL Transformation project that inserts data into a Sybase IQ table.

1. From the Navigator, right-click SQL Transformation Projects and select New > SQL Transformation Project.
2. Specify the database connection parameters. If you have a previously defined connection profile for the database connection, select it from the Profile list. This list displays the Sybase and ODBC profiles stored in the same repository as the project in the Database Configuration window and the Property window.
3. Drag the Task Group component onto the Design window and select Generic SQL as the task type. Enter a name and description for the task and click Create.
4. If necessary, click the output port of the Start component and drag it onto the input port of the Task Group component to create a connection. This connection is made automatically if the Create links automatically when components are added preference is selected.
5. Double-click the Task Group component to open the Task Container editor.
6. Select the task you created and click the Edit the Selected Task icon to open the Generic Transformation editor, where you can define the transformation rules. Before opening the Generic Transformation editor select one of these:
   • Critical – specify each task as critical or noncritical.
   • Single transaction – execute all tasks in a group as a single transaction.
   • Continue on error – continue processing even if an error occurs.
7. On the Target tab, select INSERT as the transformation operation.
8. Drag a destination table from the Model tab to the Destination text field. For example, DEMOUSER. SALESDETAILS.
   When you select the transformation operation and the destination table, a SQL statement is generated automatically and appear in the Transformation pane under the Generated Transformation tab.
9. Specify the source tables.

   • Click the Tables tab in the Transformation Definition pane.

   • Click the Model tab, then select and drag the three tables to subselect from onto the Design window. For example, DEMOUSER.STORES, DEMOUSER.SALES, and DEMOUSER. SALESDETAILS.

10. Specify a JOIN criteria for the added source tables based on attributes. For example, create a join between the DEMOUSER.STORES table and DEMOUSER.SALES table based on STORE_ID and between the DEMOUSER.SALES and DEMOUSER.SALESDETAILS based on the ORDER_NUM. You can specify multiple joins between attributes.
11. Click the source_table_name tab in the Transformation pane and select the attributes to add to the transformation. For example, click the Select option:

    • For STORE_ID attribute under the DEMOUSER.STORES tab.

    • For ORDER_NUM attribute under the DEMOUSER.SALES tab.
    • For TATTLED, DISCOUNT, and QUANTITY attributes under the DEMOUSER.SALESDETAILS tab.
    Note: Selected attributes in the Attributes tab must be listed in an order that matches the included attributes in the Target tab, to make sure the generated transformation is correct.
12. Go to the Attributes tab under the Transformation Definition pane and enter the functions to perform on an attribute. For example, to change the ORDER_NUM attribute to lowercase, enter lcase in the Function column.
13. Add a WHERE clause to this transformation. For example, click the DEMOUSER.STORES tab in the Transformation pane and select Where for the STORE_ID attribute. To add a filter condition, click the Filters tab and specify a filter criteria.
14. To sort the records based on a particular attribute, click the respective source_table_name tab and select the Sort option for that particular attribute.
15. Click the Generated Transformation tab to view the regenerated SQL statement. In this case, the generated transformation is updated with the JOIN, WHERE, ORDER BY clause and the attribute details. Click Save to return to the project window.
16. Drag the Decision component onto the Design window. Create a link between the Task Group component and Decision component.
17. Drag the Finish component onto the Design window. Connect the input port of this component to the Success output port of the Decision component.
18. Drag the Error component onto the Design window. Connect the input port of this component to the Error output port of the Decision component.
19. Save the project. Click Execute on the toolbar to run the project.
    Click Close after the project successfully executes.
    You can view and edit the project generated SQL script by clicking the SQL Script icon in the Properties window. However, if you edit and regenerate the script, you lose the changes added in.