topic Re: Oracle + Bulk UPSERT(Update or Insert) in Talend Studio

Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Sat, 16 Nov 2024 13:07:41 GMT

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

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Tue, 25 Jan 2011 06:24:19 GMT

Hello
The job looks like:
tOracleInput--main-->tOracleoutputBulkExec
Let's us know if you have any trouble.
Best regards
Shong

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Tue, 25 Jan 2011 06:36:23 GMT

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.

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Tue, 25 Jan 2011 07:07:54 GMT

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

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Tue, 25 Jan 2011 08:00:18 GMT

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

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Fri, 28 Jan 2011 06:42:36 GMT

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

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Fri, 28 Jan 2011 07:26:10 GMT

Hi Babu
You can use txxxTableList to iterate each table.
Best regards
Shong

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Fri, 28 Jan 2011 12:49:03 GMT

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

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Sun, 30 Jan 2011 08:11:24 GMT

Hello
Try to use the dynamic schema. see my screenshot.
Best regards
Shong

Re: Oracle + Bulk UPSERT(Update or Insert)

Anonymous — Sun, 30 Jan 2011 16:49:54 GMT

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