Icon

kn_​forum_​32016_​insert_​on_​duplicate

Insert update with KNIME only nodes by an ID

Insert update with KNIME only nodes by an ID

Insert update with KNIME only nodes by an IDhttps://forum.knime.com/t/how-can-i-accomplish-on-knime-insert-on-duplicate-key-update-statement/32016/10?u=mlauber71 Initial 3 rows to be insertedJohn/California Row to be inserted/updatedSucceeds regardless of whetherrow 4 already exists John/New YorkAdditional row to be insertedappendExclude rows fromtop tableby Stud_idappendnew rowsINCLUDE rows fromtop tableby Stud_idto see which rows have changedStud_id -> RowIDDatabasedata_initialdata_to_appendDROP TABLE IF EXISTS "PUBLIC"."data_initial";DROP TABLE IF EXISTS "PUBLIC"."data_to_append";DROP TABLE IF EXISTS "PUBLIC"."data_new_inserted";INSERT INTO "PUBLIC"."data_new_inserted"SELECT * FROM"PUBLIC"."data_initial" t1WHERE t1."Stud_id" NOT IN (SELECT DISTINCT t2."Stud_id" FROM "PUBLIC"."data_to_append" t2);INSERT INTO "PUBLIC"."data_new_inserted"SELECT * FROM"PUBLIC"."data_to_append";data_new_inserteddata_new_inserteddata_initialdata_to_appenddata_new_insertedStud_id AS PRIMARY_KEYTable Creator Table Creator Table Creator Concatenate ReferenceRow Filter Concatenate ReferenceRow Filter RowID H2 Connector DB Loader DB Loader DB SQL Executor DB SQL Executor DB Table Selector DB Reader DB Table Creator DB Table Creator DB Table Creator Insert update with KNIME only nodes by an IDhttps://forum.knime.com/t/how-can-i-accomplish-on-knime-insert-on-duplicate-key-update-statement/32016/10?u=mlauber71 Initial 3 rows to be insertedJohn/California Row to be inserted/updatedSucceeds regardless of whetherrow 4 already existsJohn/New YorkAdditional row to be insertedappendExclude rows fromtop tableby Stud_idappendnew rowsINCLUDE rows fromtop tableby Stud_idto see which rows have changedStud_id -> RowIDDatabasedata_initialdata_to_appendDROP TABLE IF EXISTS "PUBLIC"."data_initial";DROP TABLE IF EXISTS "PUBLIC"."data_to_append";DROP TABLE IF EXISTS "PUBLIC"."data_new_inserted";INSERT INTO "PUBLIC"."data_new_inserted"SELECT * FROM"PUBLIC"."data_initial" t1WHERE t1."Stud_id" NOT IN (SELECT DISTINCT t2."Stud_id" FROM "PUBLIC"."data_to_append" t2);INSERT INTO "PUBLIC"."data_new_inserted"SELECT * FROM"PUBLIC"."data_to_append";data_new_inserteddata_new_inserteddata_initialdata_to_appenddata_new_insertedStud_id AS PRIMARY_KEYTable Creator Table Creator Table Creator Concatenate ReferenceRow Filter Concatenate ReferenceRow Filter RowID H2 Connector DB Loader DB Loader DB SQL Executor DB SQL Executor DB Table Selector DB Reader DB Table Creator DB Table Creator DB Table Creator

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2021-04-10
On NodePit since: 2021-04-10
Last update: 2024-04-27
Created with KNIME version: v4.7.8
Tags: h2referncerowfilterprimarykeyprimary key

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!