Use a variable or table component in SQL query

[Dominik_Poignee]

I have temporary data stored in a variable, which is also displayed in a table component.

I want to store this data in a Retool managed database. I have managed to insert the individual records by calling a query on every item of the data:
INSERT INTO spec_table (
name,
instrument,
minimum,
maximum,
is_reference,
unit,
text_value,
is_numeric
)
VALUES
(
{{spec.name}},
{{spec.instrument}},
{{spec.minimum}},
{{spec.maximum}},
{{spec.is_reference}},
{{spec.unit}},
{{spec.text_value}},
{{spec.is_numeric}}
)
RETURNING spec_id

But it would make much more sense to manage this in one query, which I tried like this:

INSERT INTO
spec_table ("name", instrument, minimum, maximum, is_reference, unit, text_value, is_numeric)
SELECT
"name",
instrument,
minimum,
maximum,
is_reference,
unit,
text_value,
is_numeric
FROM {{selectedSpecifications.value}};

This generates an error: syntax error at or near "$1"

Do I need to map this array somehow to use it?

[Milan_Kalem]

Hi @Dominik_Poignee Welcome to the community

Since you are trying to push the data into retool database table, I suggest using GUI method instead of writing the query.

Steps to Perform Bulk Inserts Using Retool GUI:

1. Configure the Table Component:

• Ensure your table component is displaying the data you want to insert into the database.

2. Create a Bulk Insert Query:

• Go to the query editor and create a new query. Choose your database and select the "Bulk Insert" operation if your database supports it.

3. Use the Bulk Insert GUI:

• In the Retool query editor:
  • Choose the "Bulk Insert" operation if available.
  • Select the target table (e.g., spec_table).
  • Map the table fields to the columns in your dataset or use the whole table if all columns match.

4. Map Data from Table Component to Bulk Insert:

• Use the GUI to bind the input fields. This binding should map the data from the table component to the corresponding columns in the bulk insert operation.

5. Trigger the Bulk Insert Query:

• Add a button component to your Retool app.
• Set the button to trigger the bulk insert query when clicked.

Additionally check the values of the columns and make sure that the format of those match the columns in the table you are trying to insert the data to.

Hope this helps

[Dominik_Poignee]

Hi @Milan_Kalem !
Thank you for the answer! I am travelling at the moment, so a bit late with my testing and reply.

I have tried your suggestion, but I can't seem to see how to map the data as in your step 4. If I run the query as it is, I just get a error: duplicate key value violates unique constraint "Specifications_pkey". Which I don't quite understand as I don't have a key field in the source table.

[Milan_Kalem]

What you can do there is use map method to create an array of objects where each object contains the desired columns from each row.
{{table2.data.map(x => ({ name: x.name, id: x.id, column1: x.column1, column2: x.column2 }))}}

[Dominik_Poignee]

Thank you so much! I got it. The map method works like a charm. I assume I can now get the inserted IDs in the success handler?

[Milan_Kalem]

What I usually tend to do is have a separate query that gets the contents from the table I insert records into, and have that run via success handler.

[Dominik_Poignee]

How would you write a query to select the recently added rows?

[Milan_Kalem]

you can use something like this if you have created timestamp

SELECT *
FROM your_table_name
WHERE created_at >= NOW() - INTERVAL '30 seconds';

[Dominik_Poignee]

Thank you, @Milan_Kalem ! I have managed to use the auto-generated ID to get the newly inserted records as I know the number of records inserted.

[Milan_Kalem]

Nice, glad you got it to work!