Scenario: Using the Oracle MERGE function to update and add data simultaneously - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This scenario describes a Job that allows you to add new customer information and update existing customer information in a database table using the Oracle MERGE command.

Linking the components

  1. Drop the following components from the Palette to the design workspace: tELTOracleInput, tELTOracleMap, and tELTOracleOutput.

  2. Label tELTOracleInput as new_customer, tELTOracleMap as ELT Mapper, and tELTOracleOutput as merge_data.

  3. Link tELTOracleInput to tELTOracleMap using a Row > New Output (table) connection.

    In the pop-up box, enter NEW_CUSTOMERS as the table name, which should be the actual database table name.

  4. Link tELTOracleMap to tELTOracleOutput using a Row > New Output (table) connection.

    In the pop-up box, enter customers_merge as the name of the database table, which holds the merge results.

Configuring the components

  1. Double-click the tELTOracleInput component to display its Basic settings view.

  2. Select Repository from the Schema list, click the [...] button preceding Edit schema, and select your database connection and the desired schema from the [Repository Content] dialog box.

    The selected schema name appears in the Default Table Name field automatically.

    In this use case, the database connection is Talend_Oracle and the schema is new_customers.

    Note

    In this use case, the input schema is stored in the Metadata node of the Repository tree view for easy retrieval. For further information concerning metadata, see Talend Studio User Guide.

    You can also select the input component by dropping the relevant schema from the Metadata area onto the design workspace and double-clicking tELTOracleInput from the [Components] dialog box. Doing so allows you to skip the steps of labeling the input component and defining its schema manually.

  3. Click the tELTOracleMap component to display its Basic settings view.

  4. Select Repository from the Property Type list, and select the same database connection that you use for the input components.

    All the database details are automatically retrieved.

    Leave the other settings as they are.

  5. Double-click the tELTOracleMap component to launch the ELT Map editor to set up the data transformation flow.

    Display the input table by clicking the green plus button at the upper left corner of the ELT Map editor and selecting the relevant table name in the [Add a new alias] dialog box.

    In this use case, the only input table is new_customers.

  6. Select all the columns in the input table and drop them to the output table.

  7. Click the Generated SQL Select query tab to display the query statement to be executed.

    Click OK to validate the ELT Map settings and close the ELT Map editor.

  8. Double-click the tELTOracleOutput component to display its Basic settings view.

    From the Action on data list, select MERGE.

    Click the Sync columns button to retrieve the schema from the preceding component.

    Select the Use Merge Update check box to update the data using Oracle's MERGE function.

  9. In the table that appears, select the check boxes for the columns you want to update.

    In this use case, we want to update all the data according to the customer ID. Therefore, select all the check boxes except the one for the ID column.

    Warning

    The columns defined as the primary key cannot and must not be made subject to updates.

  10. Select the Use Merge Insert check box to insert new data while updating the existing data by leveraging Oracle's MERGE function.

    In the table that appears, select the check boxes for the columns into which you want to insert new data.

    In this use case, we want to insert all the new customer data. Therefore, select all the check boxes by clicking the Check All check box.

  11. Fill the Default Table Name field with the name of the target table already existing in your database. In this example, fill in customers_merge.

  12. Leave the other parameters as they are.

Executing the Job

  1. Press Ctrl + S to save the Job.

  2. Press F6 to run the Job.

    The data is updated and inserted in the database. The query used is displayed on the console.