One Star

How to make "insert table" wihtout duplicate input data?

Hi,
I load data from table 1 to table 2. And every time I run Talend job, it will insert duplicate data to table 2. How can I make this to throw exception or SQL constraint?
Thanks,
Vincent
14 REPLIES
Community Manager

Re: How to make "insert table" wihtout duplicate input data?

Hello
You should select 'Update or Insert' on 'Action on data' list.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to make "insert table" wihtout duplicate input data?

I have the same problem and I can't resolve it with the solution of Shong because my table have only one column and this is the key so the generated update request make a runtime error :
UPDATE matable SET WHERE monchamp='mavalue'
One Star

Re: How to make "insert table" wihtout duplicate input data?

Laurie,
Your SQL is in the wrong format.
I believe it should be something like:
UPDATE matable SET column='value' WHERE monchamp='mavalue'
One Star

Re: How to make "insert table" wihtout duplicate input data?

SMaz,
Yes I know it's the wrong format !!!
This is the code generated by Talend in case of update table with only key columns.
One Star

Re: How to make "insert table" wihtout duplicate input data?

Hi
Why don't you use a lookup for keys that already exist in the table, and just perform the insert. If there is too much data for a lookup, I normally code my source query to exclude keys already in the target. Let the DBMS do the work for you.
philip
One Star

Re: How to make "insert table" wihtout duplicate input data?

Hello laurie,
I can realy understand you, I have this problem very often (but every time solved).
Shong gave me the hint to look at the advanced tab. Set the option "Use field option". And at this point it takes me some tests to set the combination that works to solve the problem (but actual I haven't understand what the effect of my changes to the generated SQL are...)
Hope this helps you too.
Bye
Volker
One Star

Re: How to make "insert table" wihtout duplicate input data?

I know the Fields Options but in my case it doesn't work because all my table columns are part of the primary key so I don't have updatable columns and the consequence is a bad generated SQL request.
In my table, I have 2 colums taht are keys in update and non updatable.
The generated request is :
java.sql.PreparedStatement pstmtUpdate_tMSSqlOutput_1 = conn_tMSSqlOutput_1
.prepareStatement("UPDATE
SET WHERE = ? AND = ?");
One Star

Re: How to make "insert table" wihtout duplicate input data?

Hello laurie,
can you please give me a short example of the table structure, the attributes of your dataflow and your data?
Bye
Volker
One Star

Re: How to make "insert table" wihtout duplicate input data?

It is working for me.
When I check for the key column (my only column) the option ?Key in update? the SQL changes from ?UPDATE table SET WHERE key = ?? to ?UPDATE table SET key = ? WHERE key = ??
It may execute unnecessary SQL but it prevents trying to insert duplicate rows.
Thanks Volker, I read your post when browsing the forum and run into the problem 5 minutes, after when I continued working on my project. This, I call timing.
One Star

Re: How to make "insert table" wihtout duplicate input data?

All apologies, you're right, if I check the key column as "Key in update" and "Updatable" it works...
But there is an unecessary update that is done.
Thanks for your help Vaiko and Volker.
But I think that an action on data "Insert if not exists" can be interesting to manage this case and other case when you don't want to update your data.
One Star

Re: How to make "insert table" wihtout duplicate input data?

Hello laurie,
please add a feature request in the BugTracker. Better than "Insert if not exist" would be "Ignore duplicate key error on insert" I think. Your version sound like a (unnecessary) select will be executed before the insert.
Bye
Volker
One Star

Re: How to make "insert table" wihtout duplicate input data?

Hi Volker,
I've already did it : http://www.talendforge.org/bugs/view.php?id=4818
Laurie
One Star

Re: How to make "insert table" wihtout duplicate input data?

This hint below is very useful.
"Shong gave me the hint to look at the advanced tab. Set the option "Use field option" ... "
Took me a couple of hours to work that out.
One Star

Re: How to make "insert table" wihtout duplicate input data?

Hi,
could you please provide the "insert if not exists" functionality also for the tOracleOutput component? (I have also a unique constratint which
give an ORA-... error when someone tries to insert a duplicate...)
That would help a lot as Oracle is the industry standard...
Thanks,
dstefane