Upsert

Upsert data in Database Table using Update and Insert Operation

In this tutorial we will learn how to upsert  data in database table using Insert and Update operation because there is no out of box functionality available to upsert the data  in database another alternative is to use DB Merge operation to merge the data in database.

Create a project in anypoint studio

Sample Data to be inserted/updated in database table

[
  {
    "CUSTOMER_ID": 1,
    "CUSTOMER_NAME": "TEST1",
    "ADDRESS1": "Flat 101",
    "ADDRESS2": "Viman Nagar",
    "POSTALCODE": "201018",
    "CITY": "PUNE"
  },
  {
    "CUSTOMER_ID": 2,
    "CUSTOMER_NAME": "TEST2",
    "ADDRESS1": "Flat 102",
    "ADDRESS2": "Viman Nagar 1",
    "POSTALCODE": "201019",
    "CITY": "PUNE"
  },
  {
    "CUSTOMER_ID": 3,
    "CUSTOMER_NAME": "TEST3",
    "ADDRESS1": "Flat 103",
    "ADDRESS2": "Viman Nagar",
    "POSTALCODE": "201018",
    "CITY": "PUNE"
  },
  {
    "CUSTOMER_ID": 4,
    "CUSTOMER_NAME": "TEST4",
    "ADDRESS1": "Flat 104",
    "ADDRESS2": "Viman Nagar 4",
    "POSTALCODE": "201019",
    "CITY": "PUNE"
  }
]

Drag and drop the Transform message and prepare the Input for testing

Drag and drop select operation and wrap inside foreach loop and configure the DB connector

Configure the select,insert and update operation.

Using for each loop we will process all records one by one in database, first we will select the count for the each single record in database if the record exist we will update the record else we will insert the record in DB. We will use choice router to check data is present in db or not using record count variable

Select configuration

Update Operation:

Insert Operation:

Deploy the application and test

download sample project