Performing Update and Upsert Operations Using a SQL Transformation Project

Create a sample SQL Transformation project that performs update and upsert operations.

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 UPDATE as the transformation operation to perform an update operation and select UPSERT to perform an upsert operation.
   Note: Note: For improved performance in Sybase IQ, the UPSERT functionality is designed as a delete and an insert operation. To create a traditional UPSERT functionality, use the Custom SQL transformation task.
8. Select a destination table by dragging the table from the Model tab to the Destination text field.
   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. Select the attributes of the target table to update. For an update operation, you may also enter an expression or an update value for each attribute.
   Note: By default, source attributes are mapped to target attributes based on the order of attributes listed in the Attributes tab.
10. For key based updates, select the key columns. This is optional in case of an update operation but manadatory for an upsert operation.  For upsert, the key is used as a join index to delete from the destination before data is inserted from the source.
    Note: All attributes with the Key or Include options selected, are inserted.
11. Specify the source tables.

    • Click the Tables tab.

    • Click the Model tab, select, and drag a source table onto the Design window.

      Note: Source query attributes must be added to the Attributes tab in an order matching the included attributes in the Target tab, for proper mapping and key join generation.
12. In the Transformation pane select the source_table_name tab to view the table columns. For update, select the columns to use to match the key columns selected on the destination table. For upsert, select the columns to use for insertion and to match the key columns selected on the destination table.
13. (Optional) Select the Where option for columns on which you want to add a WHERE clause. To add a filter condition, click the Filters tab and specify a filter criteria.
14. Click the Generated Transformation tab to view the regenerated SQL statement. Click Save to go back to the project window.
15. Drag the Decision component onto the Design window. Create a link between the Task Group component and Decision component.
16. Drag the Finish component onto the Design window. Connect the input port of this component to the Success output port of the Decision component.
17. Drag the Error component onto the Design window. Connect the input port of this component to the Error output port of the Decision component.
18. Save the project. Once the project is saved successfully, click Execute on the toolbar to run the project.
    Click Close.Close after the project successfully executes.
    You can view and edit the project generated SQL script at any point of time by clicking the SQL Script icon in the Properties window. However, if you edit and regenerate the script, you lose the changes added in.