topic Re: How to check if a record Exists in DB in Talend Studio

How to check if a record Exists in DB

Anonymous — Tue, 17 Jul 2012 19:43:19 GMT

I have a folder of excel files
Talend package reads the folder and for each excel file in the folder it unpivots the content of the excel file and inserts in a MSSQL database.
Now the same row/record could exist in multiple files and thats why we keep getting duplicates in the database.
if someone could help me understand, if there is a way to check if the record exists in MSSQL DB, before inserting then i can skip duplicates and just get unique records. Please see the attached screenshot.
Thanks

Re: How to check if a record Exists in DB

Anonymous — Tue, 17 Jul 2012 20:08:59 GMT

Can someone from the expert talend team point me in right direction please. I would highly appreciate it.

Re: How to check if a record Exists in DB

Anonymous — Tue, 17 Jul 2012 20:10:25 GMT

Can you add a unique key to your database table? If you then set the MSSQL component to be "Insert if not exist", this would stop duplicates being inserted.

Re: How to check if a record Exists in DB

Anonymous — Tue, 17 Jul 2012 20:21:14 GMT

Thank You. Very Helpful. What should i do if i want to update the existing record instead of skipping it? Is there an option in the tMSSQL component for this as well?

Re: How to check if a record Exists in DB

Anonymous — Tue, 17 Jul 2012 20:27:55 GMT

OK - set the action on data to be "Update or insert". This updates existing entries or create it if non existing.

Re: How to check if a record Exists in DB

_AnonymousUser — Wed, 10 Jul 2013 02:03:47 GMT

OK - set the action on data to be "Update or insert". This updates existing entries or create it if non existing.

Hi,
For the Update or insert otpion in mysql output it requires a primary key. So how to get the primary keys from db and assign it to the rows in excel sheet. Kindly help.

Thanks,
Senthil

Re: How to check if a record Exists in DB

Anonymous — Wed, 10 Jul 2013 04:50:17 GMT

Hi Senthil,
I have replied your related forum https://community.talend.com/t5/Design-and-Development/how-to-fetch-primary-keys-from-db-and-set-it-to-tmap-entries/td-p/113170, feel free post your issue on forum.
Best regards
Sabrina

Re: How to check if a record Exists in DB

Anonymous — Tue, 17 Dec 2013 06:45:35 GMT

hi

"insert if not exist" option is not available in tverticaoutput component(TOS) how to achieve this case in vertica database.

Re: How to check if a record Exists in DB

Anonymous — Tue, 17 Dec 2013 07:35:08 GMT

Hi,
So far, there is no "insert if not exist" option in tverticaoutput.
As alternative, you can set any primary key you wanted in schema setting by manual(checkout the "Key" option) .
and take the "Update or insert" data action in tverticaoutput.
Best regards
Sabrina

Re: How to check if a record Exists in DB

_AnonymousUser — Wed, 28 Sep 2016 19:18:18 GMT

Hi,
I want to convert excel sheet to mysql table.MySQl table has id which is auto increment.
I have list of user in excel sheet which might contain duplicate.I have Insert ignore option which give me correct record but when add new record in existing excel file . auto increment value is almost double. for example
I have list of user 20 distinct values first time in excel file but i have added one more and try to run same excel file . the auto increment value for new record will be 41 but i want 21. is it posible?
please let me know..its very urgent.
Thank you in advance.

Re: How to check if a record Exists in DB

Anonymous — Thu, 29 Sep 2016 05:16:24 GMT

You can dump the data from all the excel files in 1 table. Post that you can only move unique or latest records in your actual table.

Re: How to check if a record Exists in DB

mmctony — Wed, 21 Sep 2022 09:54:42 GMT

Hi,

I have to perform "insert if not exist" into tables that don't have any primary key. I cannot edit database tables' definition in order to add primary keys.

If I manually set primary key fields in the schema retrieved by Talend, will Talend be able to avoid creating duplicates of existing rows then?

Thanks for your help,

Mick

Re: How to check if a record Exists in DB

Anonymous — Thu, 22 Sep 2022 03:28:41 GMT

Hello,

We can add a combobox called ACTION on the tDBOutput component.

ACTION properties will be "INSERT", "UPDATE", "INSERT OR UPDATE" or "UPDATE OR INSERT".

"INSERT OR UPDATE" means if the insertion failed, update operation will be perform.

Best regards

Sabrina