[
https://issues.apache.org/jira/browse/NIFI-14869?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18014632#comment-18014632
]
David Handermann commented on NIFI-14869:
-----------------------------------------
Thanks for describing the issue [~adelwageih]. The proposed SQL sounds like a
good general way forward. On the implementation path, this is a good
opportunity to implement a new MSSQL version of the DatabaseDialectService,
which is the intended replacement for the internal DatabaseAdapter approach.
> 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
> Priority: Major
> Labels: MSSql, putdatabaserecord, upsert
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> 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)
