[resolved] Postgress Bulk insert and Update

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

Accepted Solutions
Moderator

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

All Replies
Moderator

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

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch