topic Re: How to perform Upsert(update or insert) fast. in Talend Studio

How to perform Upsert(update or insert) fast.

Anonymous — Mon, 11 Nov 2019 15:04:29 GMT

I want to load(update or insert) lots of data. update or insert option is slow (40 rows/sec).

Is there any faster way to perform upsert(update or insert).

Source DatabaseQL Server

Destination Database: postgreSQL

Re: How to perform Upsert(update or insert) fast.

manodwhb — Tue, 12 Nov 2019 05:18:18 GMT

@vishalw007 , you can use Bulk component to load into postgresql.

tPostgresqlOutputBulkExec

https://help.talend.com/reader/jomWd_GKqAmTZviwG_oxHQ/cKJ1KqGuvlko6HlkBYdlPQ

Re: How to perform Upsert(update or insert) fast.

Anonymous — Tue, 12 Nov 2019 06:13:05 GMT

In tPostgresqlOutputBulkExec we can only perform bulk insert or bulk update. There is no option which performs update or insert operation on data.
Do you have any idea how can I perform update or insert(UPSERT) using tPostgresqlOutputBulkExec?
Any other ideas using which I can do this task?

Thanks for replying.

Re: How to perform Upsert(update or insert) fast.

manodwhb — Tue, 12 Nov 2019 07:23:45 GMT

@vishalw007 , you can split the process into two flows like insert and update separately and use the bulk component to improve performance.

Re: How to perform Upsert(update or insert) fast.

Anonymous — Tue, 12 Nov 2019 08:49:50 GMT

Thanks for the quick response.
I will try this method

Re: How to perform Upsert(update or insert) fast.

krayapudi1643622390 — Wed, 19 Apr 2023 07:41:42 GMT

Hi ,

can you help me with these questions

is it still the same way that we can’t use both insert or update during bulk operation ?

how to find out whether record already exits in table or not using tMap ?

Re: How to perform Upsert(update or insert) fast.

jlolling_kloeckner — Wed, 19 Apr 2023 10:23:38 GMT

PostgreSQL and MySQL tDBOutput components have the option to use insert extensions.

Please take care your table has an appropriated primary key and than you can use the Action on Data: "Insert or update on duplicate key and unique index".

Both database types have such options slightly different implemented but with the same result.