Icon

kn_​example_​db_​h2_​primary_​key

Example of H2 database - handling of database structure and use of Primary Keys - insert only new lines by ID

Example of H2 database - handling of database structure and use of Primary Keys - insert only new lines by ID

Example of H2 database - handling of database structure and use of Primary Keys - insert only new lines by ID -- create table in code from scratchDROP TABLE IF EXISTS "PUBLIC"."sample_01"; CREATE TABLE "PUBLIC"."sample_01" ( ID VARCHAR NOT NULL COMMENT 'id = Primary id (non auto-increment)' , v_sale DOUBLE COMMENT 'v_sale = amount of sale in $' , v_cluster BIGINT COMMENT 'v_cluster = cluster of customer' , v_name VARCHAR COMMENT 'v_name = name of customer' , due_date TIMESTAMP COMMENT 'due_date = due date' , PRIMARY KEY (ID) ) ; -- 'manually' insert values into a tableINSERT INTO "PUBLIC"."sample_01" (ID, v_sale, v_cluster, v_name, due_date)-- ID VARCHAR-- v_sale DOUBLE-- v_cluster BIGINT-- v_name VARCHAR-- due_date TIMESTAMPVALUES ('Santa Claus 2020-01-02 11:00:00', 45.45 , 2, 'Santa Claus' , '2020-01-02 11:00:00') , ('Mary Jane 2020-01-03 13:00:00', 34.8965 , 4, 'Mary Jane' , '2020-01-03 13:00:00') , ('Peter Parker 2020-01-04 12:00:00', 984.75 , 4, 'Peter Parker' , '2020-01-04 12:00:00'); DROP TABLE IF EXISTS "PUBLIC"."sample_05";CREATE TABLE "PUBLIC"."sample_05" AS (SELECT * FROM "PUBLIC"."sample_01" WHERE 1=2); INSERT INTO "PUBLIC"."sample_01" SELECT * FROM "PUBLIC"."sample_05"WHERE ID NOT IN (SELECT DISTINCT ID FROM"PUBLIC"."sample_01" ) INSERT INTO "PUBLIC"."sample_05" (ID, v_sale, v_cluster, v_name, due_date)VALUES ('Santa Claus 2020-01-02 12:15:00', 45.45 , 2, 'Santa Claus' , '2020-01-02 12:15:00') , ('Mary Jane 2020-01-03 13:30:00', 34.8965 , 4, 'Mary Jane' , '2020-01-03 13:30:00') , ('Peter Parker 2020-01-04 12:00:00', 984.75 , 4, 'Peter Parker' , '2020-01-04 12:00:00') -- last line is a double; DB in memoryor as fileon a drivecreatesample_01from scratchremove table if it exists to have a clean startsample_01sample_01=> check the typemappingsample_01empty tableinsert data into table by manually declaring the relevant partitionsample_01=> check the typemappingsample_01table with initialvaluecreate a new emptytable sample_05with thestructure of an existing onesample_05=> check the typemappingsample_05empty tableinsert data intosample_05with one double line!sample_05=> check the typemappingsample_05table with initialvalueList allTablesList all Columnsof "PUBLIC"insert only those IDs intosample_01 fromsample_05where IDs do *not* matchsample_01=> check the resultssample_01with two nw linesone duplicate excludedjoin sample_01and sample_05to see which linesar duplicatessample_01with two nw linesone duplicate excluded H2 Connector DB SQL Executor DB Table Remover DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB Query Reader DB Query Reader DB SQL Executor DB Table Selector DB Reader DB Joiner DB Reader Example of H2 database - handling of database structure and use of Primary Keys - insert only new lines by ID -- create table in code from scratchDROP TABLE IF EXISTS "PUBLIC"."sample_01"; CREATE TABLE "PUBLIC"."sample_01" ( ID VARCHAR NOT NULL COMMENT 'id = Primary id (non auto-increment)' , v_sale DOUBLE COMMENT 'v_sale = amount of sale in $' , v_cluster BIGINT COMMENT 'v_cluster = cluster of customer' , v_name VARCHAR COMMENT 'v_name = name of customer' , due_date TIMESTAMP COMMENT 'due_date = due date' , PRIMARY KEY (ID) ) ; -- 'manually' insert values into a tableINSERT INTO "PUBLIC"."sample_01" (ID, v_sale, v_cluster, v_name, due_date)-- ID VARCHAR-- v_sale DOUBLE-- v_cluster BIGINT-- v_name VARCHAR-- due_date TIMESTAMPVALUES ('Santa Claus 2020-01-02 11:00:00', 45.45 , 2, 'Santa Claus' , '2020-01-02 11:00:00') , ('Mary Jane 2020-01-03 13:00:00', 34.8965 , 4, 'Mary Jane' , '2020-01-03 13:00:00') , ('Peter Parker 2020-01-04 12:00:00', 984.75 , 4, 'Peter Parker' , '2020-01-04 12:00:00'); DROP TABLE IF EXISTS "PUBLIC"."sample_05";CREATE TABLE "PUBLIC"."sample_05" AS (SELECT * FROM "PUBLIC"."sample_01" WHERE 1=2); INSERT INTO "PUBLIC"."sample_01" SELECT * FROM "PUBLIC"."sample_05"WHERE ID NOT IN (SELECT DISTINCT ID FROM"PUBLIC"."sample_01" ) INSERT INTO "PUBLIC"."sample_05" (ID, v_sale, v_cluster, v_name, due_date)VALUES ('Santa Claus 2020-01-02 12:15:00', 45.45 , 2, 'Santa Claus' , '2020-01-02 12:15:00') , ('Mary Jane 2020-01-03 13:30:00', 34.8965 , 4, 'Mary Jane' , '2020-01-03 13:30:00') , ('Peter Parker 2020-01-04 12:00:00', 984.75 , 4, 'Peter Parker' , '2020-01-04 12:00:00') -- last line is a double; DB in memoryor as fileon a drivecreatesample_01from scratchremove table if it exists to have a clean startsample_01sample_01=> check the typemappingsample_01empty tableinsert data into table by manually declaring the relevant partitionsample_01=> check the typemappingsample_01table with initialvaluecreate a new emptytable sample_05with thestructure of an existing onesample_05=> check the typemappingsample_05empty tableinsert data intosample_05with one double line!sample_05=> check the typemappingsample_05table with initialvalueList allTablesList all Columnsof "PUBLIC"insert only those IDs intosample_01 fromsample_05where IDs do *not* matchsample_01=> check the resultssample_01with two nw linesone duplicate excludedjoin sample_01and sample_05to see which linesar duplicatessample_01with two nw linesone duplicate excludedH2 Connector DB SQL Executor DB Table Remover DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB SQL Executor DB Table Selector DB Reader DB Query Reader DB Query Reader DB SQL Executor DB Table Selector DB Reader DB Joiner DB Reader

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: 2020-09-18
On NodePit since: 2020-12-23
Last update: 2024-11-21
Created with KNIME version: v4.3.0
Tags: h2sqlcreatefromscratchdatabaseprimarykey

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

Try NodePit Runner!