Conditional INSERT

KNIME Analytics Platform
1

Hi all,

Is there any known way to perform a conditional INSERT (into SQL DB) through KNIME?

pseudocode: INSERT into table ONLY IF RowValue1 NOT EXISTS in table.a OR ...

While the task can easily be outsourced to a script (or the condition may even be included in the SQL statement in simpler cases) and then "re-KNIME-ed" via calling the script, I wonder if there is a possibility with on-board means (aka. nodes) in KNIME.

thanks for response

best,

Michi

2

Hi Michi,

KNIME does not have a specific node for this but you can use the existing nodes to accomplish this task.

If your statement depends only on one row value you can use a subquery in the insert statement e.g.

INSERT INTO targetTable(field1)  SELECT field1 FROM myTable WHERE NOT(field1 IN (SELECT field1 FROM targetTable))

as described here.

If you want to do this for several rows you could use a Table Row to Variable Loop Start node to iterate over each row and modify the value in the basic statement with the Java Edit Variable node.

All thes generated statements are executed with the Database SQL Executor node.

Let me know if this works for you or if you need more help.

Bye,

Tobias

3

Find an example WF here: https://kni.me/w/NmMeeLYnNgJe_eub

1 Like
4

Hi @Alice_Krebs , the example you presented would be doing it at the db server, rather than through Knime.

I believe Knime’s DB Merge node would be the closest thing that Knime has. It basically does an UPSERT (Insert if not exists, update is exists).

1 Like
5

Yes, thanks for clarification! Still thought people finding this topic might find it useful.