WSO2 makes it easy to insert data into a database using a batch request approach. This efficient method allows you to group multiple insert operations into a single request, reducing the overhead and improving the overall performance of your data integration workflows.
By leveraging the batch request functionality in WSO2, you can seamlessly insert large volumes of data into your database without the need to make individual calls for each record. This batch-oriented approach minimizes the network traffic and database interactions, leading to a more scalable and responsive data integration solution.
Inserting bulk data using batch request with the help of Integration Studio into MS SQL database.
Walkthrough
Download MS SQL studio via the following URL: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Navigate the download link, and it will prompt you to sign in with a Microsoft account. If you don’t have one, you can create a new account.
Complete Installation setup & create instance in Server.
Connect to SQL Server:
- Connect to your SQL Server instance using SQL Authentication. You should see the “Connect to Server” window.
Create a Database:
- Right-click on the “Databases” in the Object Explorer on the left side.
- Choose “New Database.”
- Enter a name for your database in the “Database name” field.
- Click “OK” to create the database.
Create a Table:
- Expand the newly created database in Object Explorer.
- Right-click on the “Tables”.
- Choose “New -> Table.”
- Define the columns of your table, specifying the data type, length, and any constraints.
- Save the table by clicking “Save” or using Ctrl + S.
Note: Check your database in the Object Explorer to see if the new table is listed.
Create a Data Service Project in Integration studio: File >> New >> Integration project and give the Project name and finish.
After Created Project Click on Project: Project >> New >> Data Services Configs and give the Data services name.
Later It will create Data Service Config with data services folder Right Click on data services folder >> data services >> New >> Data Service >> Create Data Services and give the Data services name Click on finish.
Complete the data services configuration like give database credentials & configure operation, query & in advanced enable batch request etc.
Configure data source give your database details.
Configure queries here.
Add Operation Here:
In advanced setting mark on Batch Request option to enable batch request.
Build and run the artifacts.
- Right-click InsertBulkDataCompositeExporter and click Export Project Artifacts and Run
- You will see the following dialog box. Select the InsertBulkDataCompositeExporter folder in the artifact list and click Finish.
In the console will get the project deploy successfully logs. In right side runtime services, it will give WSDL URL as below.
Open SoapUI click on SOAP option there give the WSDL URL, what we copied from runtime services in integration studio as below click ok.
Based on URL it will generate the data service.
Navigate createEmployee_batch_req & give the input payload request to insert data into database in below snippet input request few data are mentioned.
Navigate Green button to send a request, after inserted data successfully will get 202 status.
Open Microsoft SQL Server Management Studio and execute a SELECT query to verify if the data has been inserted into the database.
select * from EmpData.dbo.Bulkdata
Successfully inserted data. For more information, please write to [email protected].
