One Star

Oracle + Bulk UPSERT(Update or Insert)

Hi,
How to do a bulk upsert from one oracle table to other oracle table using talend?
Regards,
Babu.
9 REPLIES
Community Manager

Re: Oracle + Bulk UPSERT(Update or Insert)

Hello
The job looks like:
tOracleInput--main-->tOracleoutputBulkExec
Let's us know if you have any trouble.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle + Bulk UPSERT(Update or Insert)

Hi Shong,
This looks fine if action on data is either to insert or to update. "tOracleoutputBulkExec" doesn't support "insert or update". Only tOracleOutput has that option of "insert or update". Please find the details in the attached pics.
Regards,
Babu.
One Star

Re: Oracle + Bulk UPSERT(Update or Insert)

Hi Shong,
Requirements:
I need to transfer a set of tables present in one oracle DB to other oracle DB.
Process:
1. Pick up a table(source table) from the list, transfer data to the target table.
2. During data transfer, if data is present in the target table then it should be updated else it should be inserted.
3. Finally counts should be captured like records inserted, records updated, records rejected.
repeat untill all source tables are transfered to target.
Please suggest how to achieve this?
Regards,
Babu
Community Manager

Re: Oracle + Bulk UPSERT(Update or Insert)

Hello
The tOracleOutput can fit your request, there is no 'insert or update' action on tOracleoutputBulkExec. There are some global variables on tOracleOutput which counts the number of inserted records, updated records. Such as:
((Integer)globalMap.get("tOracleOutput_1_NB_LINE_INSERTED"))
tOracleInput--main--tOracleOutput_1
|
onsubjobok
|
tJava
on tJava, print the number of inserted records:
System.out.println(((Integer)globalMap.get("tOracleOutput_1_NB_LINE_INSERTED")));
To use these global variable, just press ctrl+blank space, you will see a list of global variable, select the proper one.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle + Bulk UPSERT(Update or Insert)

Hi,
If i have mutliple tables(say 10 tables) to be transfered then can i repeat this process using some kind of loop(one table at a time) or should i have individual set of such components for each table?
Regards,
Babu
Community Manager

Re: Oracle + Bulk UPSERT(Update or Insert)

Hi Babu
You can use txxxTableList to iterate each table.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle + Bulk UPSERT(Update or Insert)

Hi shong,
I have tried using tOracleTableList but it failed when it came to second table bcoz dynamically syncing columns for the tables is difficult.
For the first table it will work fine when it comes to second table, only table name will be of the second table but columns will be of first table and it fails.
How can we dynamically sync columns for the tables?
Regards,
Babu
Community Manager

Re: Oracle + Bulk UPSERT(Update or Insert)

Hello
Try to use the dynamic schema. see my screenshot.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle + Bulk UPSERT(Update or Insert)

Hi Shong,
I am using Talend Open Studio (4.1.1). There is no dynamic schema option to select from the drop down provided. Please find the details in the picture attached. Is that option of dynamic schema available in this version of Talend? If not then how to proceed ahead. Thanks in advance.
Regards,
Babu