One Star

Insert only if the row doesn't Exists

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!
23 REPLIES
One Star

Re: Insert only if the row doesn't Exists

Use Insert.
One Star

Re: Insert only if the row doesn't Exists

tried that one too.. no luck
One Star

Re: Insert only if the row doesn't Exists

check out this screenshot!
One Star

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

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
One Star

Re: Insert only if the row doesn't Exists

more explaination given in the screenshot below:
One Star

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

You've got nothing in your output key field.
One Star

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

what Global Variable? You never mentioned it.
One Star

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

@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!
One Star

Re: Insert only if the row doesn't Exists

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

Re: Insert only if the row doesn't Exists

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!
One Star

Re: Insert only if the row doesn't Exists

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.
One Star

Re: Insert only if the row doesn't Exists

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!
One Star

Re: Insert only if the row doesn't Exists

Well that obviously doesn't work as you're not finding duplicates.
When you output to your lookup table you will generate duplicates as it is keyed on an auto generated id so you will never get duplicate keys.
One Star

Re: Insert only if the row doesn't Exists

my concern is not the duplicate keys, but the other attributes of the table. please check the screenshot of mySQL results, you will see there are duplication and I don't want them to be repeatedly inserted.
One Star

Re: Insert only if the row doesn't Exists

So make them key fields or check to see if the values exist before inserting the record.
One Star

Re: Insert only if the row doesn't Exists

1. How can I make the key fields?
2. That's why I wrote the SQL Query with a lot of GlobalMap.Get variables to check if they exists.. but after that I don't know how to proceed?
please let me know on both of the solutions how to implement?