How to perform Upsert(update or insert) fast.

[Anonymous]

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

[manodwhb]

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

tPostgresqlOutputBulkExec

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

[Anonymous]

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.

[manodwhb · Accepted solution]

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

[Anonymous]

Thanks for the quick response.
I will try this method

[krayapudi1643622390]

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 ?

[jlolling_kloeckner]

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.