topic Prevent tmap from inserting duplicate rows in Talend Studio

Prevent tmap from inserting duplicate rows

Anonymous — Fri, 15 Mar 2013 16:48:32 GMT

Hallo,
I d like to fill up a mysql db by with some data from excel.
In the excel sheet, there are f.e. 100 rows. In two
columns I have stored the zone_id and the zone_name.
There are only 5 zones.
If I create a job with tmap and tmysqloutput, I get
in my mysql-zone table 100 entries.
But I only want the entries for the 5 different zones
occuring in the source data in Excel.
How can I prevent talend from inserting duplicate rows in my mysql table?
Yours
Matthias

Re: Prevent tmap from inserting duplicate rows

Anonymous — Sat, 16 Mar 2013 04:42:21 GMT

Hi Matthias
If you just want to remove the duplicate rows, you can use tUniqRow to get the unique rows, please read online component manual:
https://help.talend.com/search/all?query=tUniqRow&content-lang=en
If you want to do some aggregation operators based on the two columns zone_id and zone_name, see
tAggregateRow
Shong

Re: Prevent tmap from inserting duplicate rows

Anonymous — Sun, 17 Mar 2013 13:25:12 GMT

Unfortunately not as simple way. You can choose insert and update as data action.
I would like to have an option to insert with check if insert is possible and otherwise reject it without throwing Exceptions.

Re: Prevent tmap from inserting duplicate rows

Anonymous — Mon, 18 Mar 2013 02:06:43 GMT

Unfortunately not as simple way. You can choose insert and update as data action.
I would like to have an option to insert with check if insert is possible and otherwise reject it without throwing Exceptions.

Hi Jlolling
Not sure you have noticed this option 'insert ignore' in action on data, if the key already exists, ignore this record without throwing the exception, otherwise, insert this record.
Shong

Re: Prevent tmap from inserting duplicate rows

_AnonymousUser — Tue, 19 Mar 2013 09:47:22 GMT

Thank you for your advice,
tuniquerow worked very well but I could not get a result by setting tmysqloutput to 'insert ignore'.
Matthias

Re: Prevent tmap from inserting duplicate rows

Anonymous — Wed, 20 Mar 2013 03:59:01 GMT

Thank you for your advice,
tuniquerow worked very well but I could not get a result by setting tmysqloutput to 'insert ignore'.
Matthias

yes, with 'insert ignore' option, the duplicated data are ignored by database server, and we can't capture the reject data. So, if want to see which data are duplicated and output them, use tUniqRow component.
Shong