Upsert 1 Million Records In Database Using MuleSoft

Author: Aditya Veer

Upsert activity in the database updates existing data and inserts new data in the database.

Upsert Query in the Database in Mule

INSERT INTO Table_Name VALUES (….) into (…) ON DUPLICATE KEY UPDATE col1=val1, col2=val2. 


Select = Select records from database

Transform Message  = 

Transform Message  = 

Define metadata according to your requirements and map them

Batch Job

Messages can be processed in batches using Mule. Within an application, you can enable a Batch Job scope, which divides messages into separate records, performs actions on each record, relays on the results, and moves the processed outcome to specific other systems or queues.

Reasons for using Batch Job

Batch Jobs include the ability to generate good reports, handle exceptions easily, and make data aggregation easier.

Batch Aggrigator

A Batch Aggregator keeps adding data to a collection until a size equal to the Batch Aggregator’s size attribute is added. 

In ‘Batch Job’ enter the details mentioned or according to your usecase

Add a ‘Batch Aggregator’ in the ‘Batch Step’

Keep the Aggregator Size to 200 and add a ‘Bulk Insert’ in it

Make the necessary configuration changes and enter ‘payload’ in the ‘Input Parameters’ field, as well as the above-mentioned upsert query.

Add a logger at ‘On Complete’ to log the data

Open the ‘Run Configuration’ window, then navigate to your mule application and select the ‘Always’ option in the ‘Clear Application Data’ window.

Run the application,

Your records will be successfully Upserted

We use cookies on this site to enhance your user experience. For a complete overview of how we use cookies, please see our privacy policy.