How to map table data for bulk insert?

How do I do X?
1

I’ve been attempting to follow the Bulk Insert example app in order to insert data (all rows) from a table widget into a MySQL database. Where I’m getting stuck is in formatting. The following line works for lists using a single item as described in the sample:
let data = (${New_Users_List.items.map(item => '${item.user_name_input.text}').join("), (")});

I have been trying to make something similar work for a table. What I have gotten to work is using a for loop to break the table into its constituent rows, inserting each row one at a time (calling a query each time) as in the following screenshot:
Screen Shot 2022-09-16 at 11.51.00 AM

However, what I would like to do is map all of the table data into SQL format, eg:
INSERT INTO sampleTable (id, value)
VALUES {{this.params.data}};
such that this.params.data looks like (id1, 100), (id2, 132), (id3, 111), etc.

Is there a better way to do this? Should I be doing multiple maps (one on a row level, one on a table level)? If so, what does that look like? I know this is a bit more of a JS question than an Appsmith question, but since it seems like something that others on the platform are likely to encounter, I thought I’d post here.

Thank you in advance for any help you can offer!

2

@dslotcavage once you have the array you want to insert, you can simply convert the array into the values using a snippet like this

Insert into users ('name', 'gender', 'age') values {{appsmith.store.users.map((user) => { return "('" + user.name + "'," "'" + user.gender + "'," + "'" + user.age + "')" }).join(",") }}
3

This is exactly what I needed. Thank you so much. There was one small error in the snippet you posted that I have corrected below for anyone who finds this in the future (missing ‘+’).
Insert into users ('name', 'gender', 'age') values {{appsmith.store.users.map((user) => { return "('" + user.name + "'," + "'" + user.gender + "'," + "'" + user.age + "')" }).join(",") }}

1 Like