What is the best way to do an insert or update using talend?
Hi,
tOracleInput -> tOracleOutput
selecting 'Action on data' as 'Insert or Update' in 'tOracleOutput' component
Is this the to do an 'insert or update' using talend? or is there any better(faster) way to do the same?
Above logic seems to be very slow. Kindly suggest a faster option to do upsert(update or insert) operation on data while loading it from one table to other.
Regards,
Malleshwar.
Hi Malleshwar
How many rows do you need to "Insert or Update"?
If the number of rows is million level, you can use tOracleOutputBulkExec instead of tOracleOutput.
Regards,
Pedro
Hi Pedro,
Using "Talend Open Studio for Data Integration 5.1.0". Number of rows is million level only (not less than that).
I tried with 'tOracleOutputBulkExec', but it doesn't have 'Insert ot Update' option for Action on data.
Pics are attached for reference.
Regards,
Malleshwar.
Hi Malleshwar Now it's time to make a decision. If you want to get better performance, you'd better use tOracleOutputBulkExce. Or use tOracleOutput. Besides, you can divide the input rows with rownum, create multi jobs to do this and run these jobs by multi thread execution. select * from (select s.*,rownum rn from stu s) where rn>=100000 and rn<=300000 Regards, Pedro
Hi Pedro,
When 'tOracleOutputBulkExce' doen't have the 'Insert or Update' option for action on data.
How to do an upsert (update or insert) using 'tOracleOutputBulkExce' component?
Kindly suggest.
Regards,
Malleshwar.
Hi Malleshwar
That's because tOracleOutputBulkExec uses "sqlldr" to do bulk load.
There are only five predefined actions in sqlldr(Append, Insert, Update, Truncate, Replace).
Doing upsert with tOracleOutputBulkExec or tOracleOutputBulk won't get better performance which need more joins with multi tables.
If you want to optimize performance, you can check Advanced Settings->Use Cursor of tOracleInput.
Regards,
Pedro
Did any approach work for bulk insert or update here. Can you please help. I have to do bulk insert or update due to performance issue. But I'm unable to figure out how to do insert or update based on input data and perform accordingly. Like the regular, tPostgresqlOutput component it has the the option 'insert or update' to select. But for the bulk, I'm not finding that option.
Please help on how to handle this.
