Author: Harshad Patil
Goal: To Upsert Data in the MS-SQL Database using Mule 4
As there’s no out-of-box Upsert operation available in Mule as part of the database connector, we will try to achieve the same with the help of the MERGE function.
Here, I will be working on the Stock table. If the item number already exists in the database, we need to perform the update operation. Otherwise, the insert needs to be executed.
Let’s first create a sample Mule project and add a database configuration.
Expose an HTTP POST method to accept the input, transform the request as required and add an update operation provided by the database connector.
To achieve the upsert functionality in mule, we will be using the MERGE statement available in the MS-SQL database.
As shown in the below figure, the condition here is on the ITEM_NBR field. If it matches with the existing records present in the STOCK table, an Update query will be executed otherwise the Insert operation will take place.