One Star

[resolved] Postgress Bulk insert and Update

Hi all,
There's a requirement were, a large file has to be inserted into the database daily. This job is been scheduled. The output component is tPostgresqlBulkExec. But using this component we can either make Bulk insert or Bulk update, but not Both. Since there's large number records, I cannot use PostgresqlOutput, because using this component it takes it takes long time to execute. I need to make insert/update (to avoid duplicate rows).
Is there any way to do this?

Thanks,
Swathi
2 ACCEPTED SOLUTIONS

Accepted Solutions
Employee

Re: [resolved] Postgress Bulk insert and Update

Hi,
You should use a tMap and check if the key are already present in the Target Database.
You will have two outputs from the tMap:
Output 1, will have rows to insert, linked to a bulk insert
Output 2, will have rows to update, linked to an update component.
regards,
Benjamin
Community Manager

Re: [resolved] Postgress Bulk insert and Update

Thanks Benjamin, can you guide me with the function that has to be used in tmap
regards,
Swathi

No a existing function can be used, you need to query the existing key from target database and do a inner job on tMap, get the matched rows for updating, the unmatched rows for inserting. eg:
tFileInputDelimited--main--tMap-----main--tPostgresqlBulkExec(for inserting)
| -----main--tPostgresqlBulkExec(for updating)
lookup
|
tPostgresInput(select key_column from target_database)
How to do a inner job on tMap and get matched/unmatched rows? see
http://www.talendforge.org/forum/viewtopic.php?pid=8873
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
4 REPLIES
Employee

Re: [resolved] Postgress Bulk insert and Update

Hi,
You should use a tMap and check if the key are already present in the Target Database.
You will have two outputs from the tMap:
Output 1, will have rows to insert, linked to a bulk insert
Output 2, will have rows to update, linked to an update component.
regards,
Benjamin
One Star

Re: [resolved] Postgress Bulk insert and Update

Thanks Benjamin, can you guide me with the function that has to be used in tmap
regards,
Swathi
Community Manager

Re: [resolved] Postgress Bulk insert and Update

Thanks Benjamin, can you guide me with the function that has to be used in tmap
regards,
Swathi

No a existing function can be used, you need to query the existing key from target database and do a inner job on tMap, get the matched rows for updating, the unmatched rows for inserting. eg:
tFileInputDelimited--main--tMap-----main--tPostgresqlBulkExec(for inserting)
| -----main--tPostgresqlBulkExec(for updating)
lookup
|
tPostgresInput(select key_column from target_database)
How to do a inner job on tMap and get matched/unmatched rows? see
http://www.talendforge.org/forum/viewtopic.php?pid=8873
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Postgress Bulk insert and Update

Thank you