I am trying to insert data from a table widget into a MySQL database using the Bulk Insert example app, but I am getting stuck on formatting the data. The provided solution only works for single items, and I need to format the table data into SQL format for bulk insertion. I am unsure if I should use multiple maps for row and table levels or if there is a better way to do it. I am looking for help in converting the table data into a SQL format for bulk insertion.
The user was trying to bulk insert data from a table widget into a MySQL database and was struggling with formatting. They were able to insert each row individually by breaking the table into its constituent rows and calling a query for each row. However, they wanted to map all of the table data into SQL format for efficient bulk insertion.
To solve the problem, the user converted the table data into an array and used a snippet to convert it into SQL format. The snippet mapped through the array and concatenated its values into SQL format using string literals. The snippet looked like this:
Insert into users ('name', 'gender', 'age') values {{appsmith.store.users.map((user) => { return "('" + user.name + "'," + "'" + user.gender + "'," + "'" + user.age + "')" }).join(",") }}
The user just needed to replace "users" with the name of their own table and the field names ("name", "gender", "age") with the names of their own fields. They were able to insert the array into the database with the correct SQL format.