Icon

DB SQL - H2 Database, Insert Data - Bulk, Load and SQL Executor

DB SQL - H2 Database, insert data in various ways. Including INSERT VALUE statement with Flow Variables

URL: DB SQL - H2 Database, Insert Data - Bulk, Load and SQL Executor - KNIME Forum (77635) https://forum.knime.com/t/which-node-to-use/77635/15?u=mlauber71
URL: Insert Coordinates as Point into PostgreSQL database - KNIME Forum (77635) https://forum.knime.com/t/which-node-to-use/77635/17?u=mlauber71
URL: Medium Blog: KNIME, Databases and SQL https://medium.com/p/273e27c9702a

DB SQL - H2 Database, insert data in various ways. Including INSERT VALUE statement with Flow Variables

https://forum.knime.com/t/which-node-to-use/77635/17?u=mlauber71

Insert data thru a INSERT VALUE statement created from Flow Variables

=> this is not recommended. Just to show how this would be done ...

INSERT INTO "PUBLIC"."my_table_001"(

"StringCol"

, "IntCol"

, "LongCol"

, "DoubleCol"

, "BooleanCol"

, "MissingValStringCol"

, "LongStringName_LongStringName_LongStringName"

, "LongDoubleName_LongDoubleName_LongDoubleName"

, "Zoned Date Time"

, "Duration"

, "Year"

, "row_id"

)

VALUES (

'$${SStringCol}$$'

, $${DIntCol}$$

, $${DLongCol}$$

, $${DDoubleCol}$$

, $${DBooleanCol}$$

, '$${SMissingValStringCol}$$'

, '$${SLongStringName_LongStringName_LongStringName}$$'

, $${DLongDoubleName_LongDoubleName_LongDoubleName}$$

, '$${SZoned Date Time}$$'

, '$${SDuration}$$'

, $${IYear}$$

, '$${Srow_id}$$'

)

Create Table If Not Exists Cities (
Id Serial Not Null Primary Key
, CSDdguid Char(16) Not Null Unique
, CSDType Varchar(10) Not Null
, Name Varchar(50) Not Null Unique
, Population2021 Integer Not Null
, GeoLocation Point Not Null
);

Simulate the situation where you have a Lat/Long information in your KNIME data

Try to bulk insert the data - fails due to special format Point ... but can be resolved with "stringtype=unspecified"

https://forum.knime.com/t/which-node-to-use/77635/22?u=mlauber71

A somewhat dirty solution to loop thru with Flow Variables

KNIME, Databases and SQL

https://medium.com/p/273e27c9702a

Test Data Generator
Map Viewer
OSM Map View
Column Filter
Column Filter
Table Row to Variable Loop Start
Math Formula (Multi Column)
Insert values - Latitude is y and Longitude is xINSERT INTO public.cities (CSDdguid, Name, CSDtype, Population2021, GeoLocation) VALUES ('$${SCSDdguid}$$', '$${SName}$$', '$${SCSDtype}$$', $${IPopulation2021}$$, '$${Scoordinate_parsed_Arr[1]}$$,$${Scoordinate_parsed_Arr[0]}$$');
DB SQL Executor
coordinate_parsed
Coordinate Parser
Cell Splitter
my_table_001string as longvarchar
DB Table Creator
resetthis is a databasein memory just to show how this could work
H2 Connector
Partitioning
my_table_005
DB Writer
Column Renamer
insert therest of the dataset
DB Insert
DB SQL Executor
DB Reader
POSTGRESCities
DB Table Remover
DELETE FROM "public"."cities";
DB SQL Executor
convert toCoordinate
Coordinate to Latitude/Longitude
DB Table Selector
INSERT INTO public.cities (CSDdguid, Name, CSDtype, Population2021, GeoLocation)VALUES ('2021A00053502008', 'Hawkesbury', 'T', 10194, '-74.611297,45.608871');=> insert manually
DB SQL Executor
Table Row to Variable Loop Start
Table Creator
not! recommendedAlso the syntax with quotes etc.might vary depending on your SQL database
DB SQL Executor
given you have Lat/Long
Latitude/Longitude to Coordinate
you can inspect the collection of loopedFlow Variables
Variable Loop End
my_table_005string as longvarchar
DB Table Creator
my_table_001
DB Table Selector
my_table_001
DB Table Remover
PW=123456additional jdbc parameter:stringtype=unspecified
PostgreSQL Connector
my_table_005
DB Table Remover
year
Extract Date&Time Fields
my_table_001
DB Reader
Row Filter (Labs)
replace($$CURRENTCOLUMN$$, "'", "''")
String Manipulation (Multi Column)
row_id
RowID
my_table_010
DB Table Remover
my_table_001
DB Reader
my_table_005
DB Writer
after 1st insert
DB Reader
fails due toPoint format
DB Insert
Column Filter
Number to String
you can inspect the collection of loopedFlow Variables
Variable Loop End
String Manipulation
after 2nd insert
DB Reader
Column Renamer
DB Reader
DB insert needs lowercasenames for PostgreSQL
Column Renamer (Dictionary)
DB Table Selector
my_table_010string as longvarchar
DB Table Creator
Extract Table Spec
bulk load
DB Loader
new column name
String Manipulation
sample_data.table
Table Writer

Nodes

Extensions

Links