topic Re: Insert only if the row doesn't Exists in Talend Studio

Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 09:13:18 GMT

Hi,
How can I make it possible to Insert a row only if it doesn't Exists in the table?
I'm using "Update or Insert" on tMySQLOutput component. But that doesn't work for me!

Re: Insert only if the row doesn't Exists

janhess — Fri, 27 Jul 2012 09:13:50 GMT

Use Insert.

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 09:15:03 GMT

tried that one too.. no luck

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 10:04:20 GMT

check out this screenshot!

Re: Insert only if the row doesn't Exists

janhess — Fri, 27 Jul 2012 11:12:59 GMT

Screen shot shows Insert or Update. Set it to Insert. Duplicate keys will be rejected.

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 11:23:47 GMT

as I mentioned earlier, it still keep inserting records even if i set it to "Insert".
following is the example data:
ID Name SRC
------------------------------------
81687 text1 source
81686 text1 source
81685 text1 source

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 11:33:43 GMT

more explaination given in the screenshot below:

Re: Insert only if the row doesn't Exists

janhess — Fri, 27 Jul 2012 11:36:20 GMT

In the tMap set the input lookup for inner join and the output to Catch lookup inner join reject to true.

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 13:57:52 GMT

thanks but it is still inserting the same records over and over again... Please check out the tMap settings in the screenshot.

Re: Insert only if the row doesn't Exists

janhess — Fri, 27 Jul 2012 14:30:09 GMT

You've got nothing in your output key field.

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 14:33:55 GMT

key_strings is the output component having row12.keywords. it has a value.

Re: Insert only if the row doesn't Exists

janhess — Fri, 27 Jul 2012 14:43:06 GMT

OK so your output is all the records that don't match the lookup.

Re: Insert only if the row doesn't Exists

Anonymous — Fri, 27 Jul 2012 14:52:46 GMT

it's fixed. I had to use the Global Variable.

Re: Insert only if the row doesn't Exists

janhess — Fri, 27 Jul 2012 15:09:20 GMT

what Global Variable? You never mentioned it.

Re: Insert only if the row doesn't Exists

_AnonymousUser — Mon, 30 Jul 2012 07:47:40 GMT

Set it to insert. Press the button repeatedly will be rejected.

Re: Insert only if the row doesn't Exists

Anonymous — Tue, 31 Jul 2012 12:17:20 GMT

@janhess > I meant the GlobalKey Map variable.
By the way: i'm having the same thing as @infoleather mentioned. rows are rejected when I run the job for second or third time. But for the first time, it is inserting everything... that means duplication is still there!

Re: Insert only if the row doesn't Exists

janhess — Tue, 31 Jul 2012 12:45:30 GMT

Sorry, don't understand what you're doing.

Re: Insert only if the row doesn't Exists

Anonymous — Tue, 31 Jul 2012 14:20:04 GMT

this is strange, i've provided all the details above.
Well.. I've just taken some screenshots of the entire job workflow including the queries and the mysql results. hope that helps in understanding!

Re: Insert only if the row doesn't Exists

janhess — Tue, 31 Jul 2012 14:29:24 GMT

Don't understand the query in image 3. What are the globalMap.Get items?
The table you are looking for duplicates in is keyed on an id so you would need to look for duplicate data items.

Re: Insert only if the row doesn't Exists

Anonymous — Tue, 31 Jul 2012 14:38:03 GMT

globalMap.Get items are setup in tMap so can be used in the Query, to find the exact match.
don't get what you mean?
"The table you are looking for duplicates in is keyed on an id so you would need to look for duplicate data items."
please be descriptive!