adel wageeh created NIFI-14869:
----------------------------------
Summary: Add UPSERT support for MS SQL Server using MERGE in
PutDatabaseRecord
Key: NIFI-14869
URL: https://issues.apache.org/jira/browse/NIFI-14869
Project: Apache NiFi
Issue Type: Improvement
Components: Extensions
Affects Versions: 2.2.0
Environment: Windows 11, Java 21.0.5, NiFi 2.2.0, MS SQL Server 2012+
Reporter: adel wageeh
Problem:
The PutDatabaseRecord processor in NiFi 2.2.0 does not support the UPSERT
statement type for MS SQL Server dialects (2008 or 2012+), resulting in a
validation error: "UPSERT not supported with Database Dialect". MS SQL Server
lacks native UPSERT but supports equivalent functionality via the MERGE
statement (available since 2008).
This limits dynamic workflows, such as data masking integrations, where upsert
operations are needed without custom scripts or workarounds.
Proposed Solution
Extend the MSSQLDatabaseAdapter to:
- Return true for supportsUpsert().
- Implement getUpsertStatement() to generate a MERGE query using the Update
Keys property for matching.
Example generated MERGE statement:
sql
MERGE INTO table_name AS target
USING (VALUES (?, ?, ?)) AS source (field1, field2, field3)
ON target.key = source.key
WHEN MATCHED THEN
UPDATE SET field2 = source.field2, field3 = source.field3
WHEN NOT MATCHED THEN
INSERT (field1, field2, field3)
VALUES (source.field1, source.field2, source.field3);
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
