Icon

kn_​example_​db_​loop_​multiple_​tables

Identify tables in you database(s) and loop over them and store the data in a CSV file

Identify tables in you database(s) and loop over them and store the data in a CSV file



Identify tables in you database(s) and loop over them and store the data in a CSV file knime://knime.workflow/data/my_database.h2STARTknime://knime.workflow/data/my_database.sqlitelist all TablesSELECT name FROM sqlite_master WHERE type='table'the rows that are not identicalfilter tables you do not wantENDlist all tablesSELECT * FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'PUBLIC'filter tables you do not want$TABLE_NAME$ LIKE "Sheet*" => TRUESTARTvar_table_schema_nameotherweise H2 would no accept the nameyou have to escape " like \"sort by namemy_results_from_h2.csvmake sure theCSV file is deletedmy_results_from_h2.csvappend the dataENDmy_results_from_sqlite.csv H2 Connector Table Row ToVariable Loop Start SQLite Connector DB Query Reader DB Table Selector DB Query Reader Rule-basedRow Filter DB Reader Loop End Create and fill SQLiteand H2 databases DB Query Reader Rule-basedRow Filter Table Row ToVariable Loop Start Java EditVariable (simple) Sorter DeleteFiles/Folders Try (VariablePorts) Catch Errors(Var Ports) Merge Variables CSV Writer Variable Loop End CSV Writer Identify tables in you database(s) and loop over them and store the data in a CSV file knime://knime.workflow/data/my_database.h2STARTknime://knime.workflow/data/my_database.sqlitelist all TablesSELECT name FROM sqlite_master WHERE type='table'the rows that are not identicalfilter tables you do not wantENDlist all tablesSELECT * FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'PUBLIC'filter tables you do not want$TABLE_NAME$ LIKE "Sheet*" => TRUESTARTvar_table_schema_nameotherweise H2 would no accept the nameyou have to escape " like \"sort by namemy_results_from_h2.csvmake sure theCSV file is deletedmy_results_from_h2.csvappend the dataENDmy_results_from_sqlite.csv H2 Connector Table Row ToVariable Loop Start SQLite Connector DB Query Reader DB Table Selector DB Query Reader Rule-basedRow Filter DB Reader Loop End Create and fill SQLiteand H2 databases DB Query Reader Rule-basedRow Filter Table Row ToVariable Loop Start Java EditVariable (simple) Sorter DeleteFiles/Folders Try (VariablePorts) Catch Errors(Var Ports) Merge Variables CSV Writer Variable Loop End CSV Writer

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: 2018-06-29
On NodePit since: 2021-12-24
Last update: 2024-04-26
Created with KNIME version: v4.5.0
Tags: sqlsqlitelooph2database

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

Try NodePit Runner!