Bulk Insert In Database Using Mule Database Connector
In this tutorial we will demonstrate how can you use Bulk Insert operation In Database Using Mule Database Connector and insert multiple rows at a time.
The insert, update, and delete operations can be used for the cases in which each input parameter can take only one value. Alternatively, bulk operations allow you to run a single query using a set of parameters values.
You can avoid unnecessary steps by doing a bulk operation so that:
- The query is parsed only once.
- Only one database connection is required since a single statement is executed.
- Network overhead is minimized.
- RDBMS can execute the bulk operation atomically.
Create a project and add HTTP listener to call the API
Drag and Drop bulk insert connector
Create a DB connection
Add the insert query as below
Set input parameter as
Now add a transformation
Here metadata for the output will be pre-populated and can see it accept the Array of the input object, here we are mapping data for two countries.
Run the application using – http://localhost:8081/insert
And now we can see the data added in Target table –
Sample application – http://mulesy.com/wp-content/uploads/2019/01/oracle-db-bulk-insert.zip
what are input parameters and sql parameters for bulk insert data from post man
It should a post request with body content like:
{ sql: "INSERT INTO TBL_A(COL1, COL2) VALUES(:val1, :val2)", params: [ {val1: 1, val2: 2}, {val1: 3, val2: 4} ] }how many inserts can bulk insert handle?
I have a file with 50K records(one record is one row in db) will it be able insert all the records?
Send them in Chunks