Write multiple rows in database from process

Question

Hello, 
 Is there a way to insert/update multiple rows ( from an array process variable) using "Query Database" or "Stored Procedure" nodes, but without generating one instance for each row to be inserted/updated? 
 Thank you.

Arun Theja Muthyalu · Accepted Answer

Hi oanas, 
 
I assume you are using MySQL database because you cannot pass an array input to Stored Procedure in MySQL. 
 
You can convert the data to JSON using a!toJson() and then pass it as a string input to the your Stored Procedure from Execute Stored Procedure Smart Service. 
 
Parse the JSON String inside your SP and insert/update the result to the respective table. 
 
You might follow the below link for learning on how to parse JSON in MySQL. 
 dev.mysql.com/.../json.html 
 
This way, it would be just one instance to insert/update multiple rows using SP. 
 
Thank you, 
Arun

aloks0189 · Answer

HI oanas I am completely agree with MikeSchmitt and bryant.st39 , you can go for any of these 2 approaches mention below:- 
 
1. Create a additional CDT and map the required properties which you want to persist/update, and use this CDT under Write to Data Store Entity Smart Service. 
 
2. If you are not interested in creating duplicate CDT, then i recommend, you can go for Stored Procedure where you can combine all the rows ID as String and send that String as input to Stored procedure, and inside stored procedure you can split and write these data into a temporary table, and can update the table accordingly. 
 
But if you are willing to Perform Insert operation as well, i suggest, you should go for Option-1 
 
Hope this will help you.