JDBC Update activity from JDBC Palette is used to update database records or insert new records. In the configuration tab of JDBC Update activity, you can write SQL query to insert new record. What if you want to insert multiple records to a database table in a batch?
In this tutorial, I will explain how you can use Batch Update feature of JDBC Update activity to insert multiple records at a time in the table.
Step 1: Create JDBC Connection
This is always the first step that you need to perform for any projects involving database interactions. From the JDBC Palette, add JDBC Connection resource in your project and configure it according to your own database configurations.
In my case configurations of JDBC Connection are shown in below screenshot:
Using the Test Connection option, confirm that your JDBC Connection is successful.
Step 2: Create Process for JDBC Batch Update to Insert Multiple Records
Create a new Process and add JDBC Update Activity in the process.
We want to add multiple Records into the table TBL_FILES which is already created in my database schema. The table has two columns (FILE_NAME and FILE_SIZE). It was created using below SQL Query:
create table TBL_FILES
FILE_NAME VARCHAR2(50) not null,
In the process, configure JDBC Update Activity by specifying the connection in the configuration tab. Also, add sql query in the SQL Statement field and add two parameters in the Prepared Parameters section as shown below:
In order to enable Batch JDBC Update, we need to check Batch Update checkbox in the Advanced tab which is unchecked by default:
After checking this Batch Update option, now if you go to Input tab, you will see that there is a * with Records element which means that you can add multiple Records.
By Right Clicking on Record and using Duplicate option from the Statement, you can add multiple Records as shown below:
You can add as many records as you want by this method. I am adding two records here in this example scenario as you can see in below screenshot:
Validate the project to make sure everything is fine. Once validated successfully, proceed to next step in which we will test JDBC Batch Update process in designer tester.
Step 3: Test JDBC Batch Update Process
Load the process in designer tester so that Its job is created. As you can see in below screenshot, process has run successfully and JDBC Update Activity output shows that 2 Records have been updated:
Now you can verify the results by going to database table and querying that. As you can see below, two records in a batch have been inserted successfully in database table:
I hope this Tutorial will be helpful for you. Contact me for any guidance or help needed.