topic Re: insert if not exist for postgreSql in Talend Studio

insert if not exist for postgreSql

Anonymous — Wed, 17 Jul 2013 10:13:15 GMT

Hello,
I am trying to merge data from mysql to postgreSql.. But I just wanna insert datas if not exist in Target table.
There is no option like "insert if not exist" in tPostgresqlOutput component.
In adition, both table has primary key and this primary keys exactly same for the data which exist in both table..
So I guess there should be a way to avoid to update datas which exist in target table..
Thanks

Re: insert if not exist for postgreSql

Anonymous — Wed, 17 Jul 2013 12:33:48 GMT

Hi ,
In tpostgresqloutput component properties.
Go to the "Action on data" and select the 'insert or update' option from drop down list.
Insert or update working like:
New record insert in target table,
Existing record updated in target table.
source table contain data like:
emp_id | emp_name
101 A
103 B
104 C
105 D
Target table contain data like:
emp_id | emp_name
101 A
103 E

select the 'insert or update' option from drop down list
Execute the job .
output become like: Target table
emp_id | emp_name
101 A
103 B
104 C
105 D
Here 104,105 are new records and 103 emp_name update with B.
Because 103 emp_name is B in source table.
i.e 103 emp_name is updated B in target table.
Regards.
kumar.talend

Re: insert if not exist for postgreSql

Anonymous — Thu, 18 Jul 2013 08:03:27 GMT

Hello Kumar,
Thank you for your reply. Target table is more up-to date than source table so I dont wanna update datas in target table. I just wanna insert datas if not exist in target table. So thats what I need is below.
source table contain data like:
emp_id | emp_name
101 A
103 B
104 C
105 D
Target table contain data like:
emp_id | emp_name
101 A
103 E

output should be like:
emp_id | emp_name
101 A
103 E
104 C
105 D

Is it possible to this using Talend?
Thanks

Re: insert if not exist for postgreSql

Anonymous — Thu, 18 Jul 2013 14:39:42 GMT

Hi,

It is possible through Query.
Source Table Name: EMP
Target Table Name: EMP1
Both Table have same schema.
Query :
"select emp_id,emp_name from EMP where emp_id in
((select emp_id from EMP) except (select emp_id from EMP1)"
Write the above query in tpostgresSqlInput Query body:

Regards
kumar.talend