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