Merges (insert/updates) all data rows from the
selected database table that match the values of a row from the selected KNIME input table columns.
All selected column names of the KNIME table need to exactly match the column names from the database.
The WHERE column values of the input KNIME data row need to match the values of the database row
to update; whereas the SET database column values are replaced by the input KNIME column values.
Only the rows matching all values of the selected column of any given input row will be updated.
If no matching rows are found, the node inserts new row into the selected database table.
The column order of the KNIME table and the database table do not need to match.
The output table contains two additional columns if Append merge statuses checkbox is checked. The first
extra columns is the number of rows affected by the MERGE statement. A number greater than or equal to
zero -- indicates that the command was processed successfully and is an update count giving the number of
rows in the database that were affected by the command's execution. A value of -2 -- indicates that the
command was processed successfully but that the number of rows affected is unknown. The second column
shows a warning message, if any.
Options
Settings
Output Type Mapping
This tab allows you to define rules to map from KNIME types to database types.
Popular Successors
- Cross Joiner6 %
Row Filter (deprecated)3 %- Variable Loop End3 %
Loop End3 %- DB Insert3 %
- Loop End3 %
- DB Merge3 %
- Excel Writer2 %
- DB Row Filter2 %
Joiner2 %- DB Connection Extractor2 %
- DB SQL Executor2 %
- DB Update2 %
- String Manipulation1 %
CASE Switch Data (End)1 %Extract Date&Time Fields1 %- End IF1 %
- PostgreSQL Connector1 %
- DB Connection Closer1 %
- DB Loader1 %
- DB Query Reader1 %
- Joiner1 %
- Merge Variables1 %
- DB Delete (Table)1 %
Excel Writer (XLS)1 %- Table View (JavaScript)1 %
Variable to Table Column< 1 %String Replace (Dictionary)< 1 %- DB Table Creator< 1 %
Flow Variable End IF< 1 %- Wait...< 1 %
Variable to Table Row< 1 %- CSV Reader< 1 %
- GroupBy< 1 %
- Sorter< 1 %
- Send Email< 1 %
- Timer Info< 1 %
- Microsoft SQL Server Connector< 1 %
- DB Looping< 1 %
- DB Concatenate< 1 %
- DB Column Filter< 1 %
- GET Request< 1 %
- Catch Errors (Data Ports)< 1 %
- Catch Errors (Var Ports)< 1 %
- Table Row to Variable< 1 %
- CSV Writer< 1 %
- Java Edit Variable< 1 %
- Concatenate< 1 %
- Constant Value Column< 1 %
- Column Filter< 1 %
Column Rename< 1 %- Column Splitter< 1 %
- Value Counter< 1 %
- Rule Engine< 1 %
- CASE Switch End< 1 %
CASE Switch Variable (End)< 1 %- Table View< 1 %
- MySQL Connector< 1 %
- DB Type Mapper< 1 %
DB Loader< 1 %- Parameterized DB Query Reader< 1 %
- DB Reader< 1 %
- DB Writer< 1 %
- DB Joiner< 1 %
- DB Query< 1 %
- DB Table Selector< 1 %
- Delete Files/Folders< 1 %
- Files/Folders Meta Info< 1 %
- Transfer Files< 1 %
- DELETE Request< 1 %
- Don't Save End (3 Ports)< 1 %
Table Row to Variable< 1 %Variable to Table Row< 1 %- Java Snippet< 1 %
Row Splitter (deprecated)< 1 %- DB Connection Table Writer< 1 %
- DB Sorter< 1 %
Table Writer< 1 %- Column Merger< 1 %
- Parallel Chunk End< 1 %
- DB Connector< 1 %
- DB GroupBy< 1 %
- DB Partitioning< 1 %
- Column Expressions< 1 %
Excel Sheet Appender (XLS)< 1 %- URI to String< 1 %
- Column Resorter< 1 %
CASE Switch Variable (Start)< 1 %- Extract Table Dimension< 1 %
Merge Variables< 1 %- DB Column Rename< 1 %
- DB Query Extractor< 1 %
- Line Chart (JFreeChart)< 1 %
- Google Sheets Updater< 1 %
- Table to JSON< 1 %
- Copy/Move Files (legacy)< 1 %
Table Row To Variable Loop Start< 1 %CSV Writer< 1 %Database Writer< 1 %- Table Writer< 1 %
Loop End (2 ports)< 1 %Recursive Loop End< 1 %Cell Replacer< 1 %Column Appender< 1 %- One to Many< 1 %
- Duplicate Row Filter< 1 %
- Reference Row Filter< 1 %
- Table Manipulator< 1 %
- Partitioning< 1 %
- Rule-based Row Filter< 1 %
- Rule-based Row Splitter< 1 %
- Cache< 1 %
- Extract Table Spec< 1 %
- Interactive Table (legacy)< 1 %
Excel Writer (XLS)< 1 %Send to Power BI< 1 %- Java Snippet (simple)< 1 %
- Java Snippet Row Filter< 1 %
Transfer Files< 1 %- Generic JavaScript View (JavaScript)< 1 %
- POST Request< 1 %
- Table Difference Checker< 1 %
- String to Date&Time< 1 %
- Create Date&Time Range< 1 %
- Date&Time-based Row Filter< 1 %
- Modify Time Zone< 1 %
- Current URL< 1 %
Views
- This node has no views
Workflows
2021_01_18_DB_MergeKNIME Forum- 2021_01_18_DB_Merge_reproducedKNIME Forum
- Aufgabe2KNIME Hub
- Aufgabe7KNIME Hub
- BausteineKNIME Hub
- Show all 21 workflows
Links
- database insert or updateby ipazin on 2018-10-26forum.knime.com/p/49137
- Data loading too slow. What will happen if I cancel the execution?by izaychik63 on 2021-10-26forum.knime.com/p/137909
- Concatenation (?): update existing rows and add new onesby izaychik63 on 2019-02-20forum.knime.com/p/54873
- Help for add only new rowsby izaychik63 on 2020-06-16forum.knime.com/p/94424
- DB2 Inserts from Hadoopby izaychik63 on 2020-05-10forum.knime.com/p/90587
- Show all 12 links
Developers
You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.