How do I insert data to MySQL from array in Retool?

Hi everyone, I need to bulk insert data into a Mysql table based on the information from another table and a dropdown. I don't have any idea how to do it...
What I do:

  1. Select multiple rows in table A.
  2. Select a value from a dropdown.
  3. Insert the ID of selected rows from table A (it will be a foreign key), a value from dropdown into table B.

Thanks for the help.

1.) Create the components. Enable multi select on table. When you do this, will be an array of all selected rows' data.

2.) Create a SQL query. In it, put some Javascript code that will pull in the table data and the dropdown data and get it into the right format.

3.) For this JS code, use the map function. If you don't know about the map function, go read up on it, it's pretty useful for most things you'll want to do. Map returns an array of objects, which luckily is what the SQL query wants. Then it's just a matter of naming each object what the database expects. In this case, this little snipped should do just fine. Of course, you'll want to substitute the name "id_field" and the name "dropdown_field" with the names your database is expecting.

{{ => ({id_field:, dropdown_field: select1.value})) }}

Check out the full picture below. Make sense?

@MarkR Hi Mark, thanks for the reply. I think it's clear (but javascript is no my strength :slight_smile: ). I will try it and will let you (and others) know. Thank you.

1 Like

@honzapav Mark’s reply above is definitely the way to go to build a re-usable app in Retool.

To do a one-time bulk insert (e.g. from a CSV) to MySQL, you can also use our Import CSV to MySQL Utility.

Just upload a CSV, make any changes to your data using the table editor, and run the auto-generated query in your MySQL client. You can also click “Run query in Retool” to auto-generate an app that writes to your database.

Let me know if you have any questions!

1 Like

Hi @MarkR – I want to thank you. I have implemented your solution, and it works like expected. Thanks a lot!

1 Like

Similar to Mark's question is it possible to pass an array into a Sql select without the Gui? I want to pass an array into a temp table, do etl on it and then insert it to a table in my database.

Hi @nicolas, yes it's possible. if you output an array from that ETL (jsquery or jstransform resource), you can follow how the docs here unnest (I use bigquery so this is most familiar term) the array to your SQL query.