topic Re: How to make "insert table" wihtout duplicate input data? in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 14:15:48 GMT

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

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

Anonymous — Thu, 07 Aug 2008 03:16:20 GMT

Hello
You should select 'Update or Insert' on 'Action on data' list.
Best regards
shong

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

Anonymous — Wed, 13 Aug 2008 16:57:32 GMT

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'

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

Anonymous — Wed, 13 Aug 2008 19:35:05 GMT

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

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

Anonymous — Thu, 14 Aug 2008 08:34:47 GMT

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.

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

Anonymous — Fri, 15 Aug 2008 15:39:01 GMT

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

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

Anonymous — Fri, 15 Aug 2008 21:37:11 GMT

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

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

Anonymous — Mon, 18 Aug 2008 09:02:59 GMT

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 = ?");

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

Anonymous — Tue, 19 Aug 2008 22:25:54 GMT

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

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

Anonymous — Thu, 21 Aug 2008 11:59:20 GMT

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.

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

Anonymous — Thu, 21 Aug 2008 13:43:49 GMT

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.

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

Anonymous — Thu, 21 Aug 2008 20:42:02 GMT

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

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

Anonymous — Thu, 21 Aug 2008 23:24:21 GMT

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

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

Anonymous — Thu, 24 Sep 2009 04:13:30 GMT

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.

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

_AnonymousUser — Wed, 25 May 2011 12:36:18 GMT

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