[
https://issues.apache.org/jira/browse/NIFI-14869?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18097426#comment-18097426
]
adel wageeh commented on NIFI-14869:
------------------------------------
Hello, [~exceptionfactory] . PR #10218 was closed due to lack of review
bandwidth, so I've opened a new PR (#11442) with the same approach, addressing
the test implementation and Checkstyle feedback you mentioned:
[https://github.com/apache/nifi/pull/11442]. Happy to make further changes
whenever you have cycles.
> 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
> Time Spent: 40m
> Remaining Estimate: 167h 20m
>
> *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". Although MS
> SQL Server does not have a native UPSERT statement, equivalent functionality
> is available via the MERGE statement. This limitation restricts dynamic
> workflows, such as data masking integrations, where upsert operations are
> required without custom scripts or workarounds.
> *Solution:*
> To address this limitation, new services were implemented:
> {{MSSQLDatabaseDialectService}} and {{{}MSSQL2008DatabaseDialectService{}}},
> both conforming to the {{DatabaseDialectService}} API. These services:
> * Indicate support for UPSERT operations by including UPSERT in the set of
> supported statement types.
> * Generate MERGE statements for UPSERT operations, utilizing table and
> column definitions as well as primary key information to ensure correct
> matching and updating/inserting of records.
> * Integrate with NiFi processors, such as {{{}PutDatabaseRecord{}}},
> enabling direct upsert operations for MS SQL Server without the need for
> custom scripts.
> * Align with NiFi’s architectural direction by replacing the legacy
> {{DatabaseAdapter}} approach with the new {{DatabaseDialectService}} API,
> providing a more maintainable and extensible solution for database dialect
> support.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
