Asmath, Upsert in SQL Server (without NiFi) can be difficult, and even error-prone if concurrency is needed [1]. I suspect that's why it hasn't been attempted in PutDatabaseRecord (well, via the MSSQL adapter(s)) as of yet. I haven't tried it without creating a procedure so I'm not sure if the MSSQL database adapter can create a standalone statement for MERGE or "try INSERT catch(error) then UPDATE".
Other DBs such as MySQL and PostgreSQL have easier ways to achieve this so NiFi has been able to support Upsert for those DBs. Rather than deleting the row, maybe you could catch any INSERT errors and route them back to the processor using UPDATE instead? Regards, Matt [1] https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/ On Thu, Mar 11, 2021 at 3:18 PM KhajaAsmath Mohammed <mdkhajaasm...@gmail.com> wrote: > > Hi, > > I am looking for some help on how to deal with upserts/updates on SQL Server > with NIFI. > > I get a flow file where the records are updated. Primary key are already > defined on the table. > > I don't want to try the logic of delete and insert, is there a way to handle > upsert automatically with this approach? This can be present for any table > and should be dynamic. > > Thanks, > Asmath
