Append SQL-Tables with Database Writer

KNIME Analytics Platform
1

Hello Knime Developer-Team,

first of all, I'd like to thank you for this great, new version of knime!
Will there be any posibility to append Data-Tables in SQL-Databases with Output-Tables from Knime in future Versions? The Database Writer always creates a new Table (and overwites an existing one).

Greetings
Frank Theisohn

2

we are working on expanding the database functionality quite a bit - once this is ready, appending columns to existing tables should be straightforward because KNIME will essentially allow to work on database views. This is pretty high on our list...
Stay tuned.

3

Knime just keeps getting better!

4

...as one of the guys over here pointed out - we will likely first have nodes that add ROWS to an existing DB, not columns. Doing that with simple DB operations may be a bit more tricky... The idea is to wrap SQL (and other) DB commands in nodes and leave the new table (e.g. the new view the command creates) inside the DB as long as possible. Only once "real" KNIME nodes start working on it will the data be transferred to the workflow.

5

I tried to append rows into an database table by doing the following:
One Node "Database Reader" gets the content of the original DB-Table. A second node "File Reader" read additional Data from an ASCII-File with the same structure. Concatenating these two Tables doesn't seem to work properly (why that?). Writing the result-table back into the database should be something like a workaround for the append-function.

Greetings Frank

6

FTheisohn wrote:
I tried to append rows into an database table by doing the following:
One Node "Database Reader" gets the content of the original DB-Table. A second node "File Reader" read additional Data from an ASCII-File with the same structure. Concatenating these two Tables doesn't seem to work properly (why that?).

You have to make sure that the data specifications of both, File Reader and DB Reader output ports, are the same. You can easily check that by right clicking on one of those nodes and open its port view. The second tab shows the DataTableSpec - which have to have the same structure. You also have to make sure that you handle duplicate row IDs; per default, duplicate rows are skipped, but you can change this option within the dialog of the Concatenate node. Last thing you can do is to check the resulting table from the Concatenate node.

FTheisohn wrote:

Writing the result-table back into the database should be something like a workaround for the append-function.

Correct, that's a way to append rows to an existing table in the database. However, appending rows to an existing database, will need to be directly supported by our Database Writer.

8

Hello Everyone,
I am looking for the same option, to append data to a SQL table with other SQL table. Is there a way in knime now? Database connection Table writer also drops a table before it writes data.

9

Hi @arunknimeuser -

Have you tried the DB Connection Table Writer (Labs) node? This node has a radio button that allows for Overwrite/Append/Fail.

Note that you’ll need to install the KNIME Database (Preview) extension to use it. Also, the DB nodes are intended to be used in conjunction with one another, so you may have to replace some of the existing database nodes in your workflow too. We’re planning to release a tool that will help with the conversion process from the old database nodes to the new DB nodes soon.

2 Likes
10

Thanks ScottF. Started exploring the Knime Database Labs and seem to be finding correct things for my requirement.

2 Likes