-
Goal: I'm trying to bulk insert records into a retool database. I transformed the data to be a nested array. I'm currently testing with only 2 columns.
-
Steps: Get data: check, transform data: check, Insert data: problem
-
Details: I'm trying to insert an nested array of data into a retool database from a workflow. I'm currently using SQL syntax for the insert. I suspect I'm using the retool variable the wrong way but I cant figure out how to use it the right way. I'm getting the error: syntax error at or near "$1". When I look at the result I'm trying to input into my database on its own it is a nested array of data. But when its in the query it shows as 1 giant array.
-
Screenshots:
-
App json export:
{"data":null,"error":{"error":true,"message":"syntax error at or near \"$1\"","position":57,"isRetoolSystemError":false,"queryExecutionMetadata":{"estimatedResponseSizeBytes":99,"resourceTimeTakenMs":97,"isPreview":false,"resourceType":"retoolDb","lastReceivedFromResourceAt":1712761726485}}}
Funny - I just wrote about this in another post. Partially quoting myself, the SQL for a multi-value insert like what you are doing is
INSERT INTO xyz (col1, col2)
VALUES ('a','b'),('c','d');
The query you are writing is not this form you have something like INSERT INTO xyz (object).
What you are likely looking to do is
INSERT INTO today_prices (cost_time, price_stock)
/* put your query4 SELECT statement here */
Hi @jg80
Thanx for your response! Sorry for antoher question but i'm trying to understand this. In my screenshots it looks to me that I have the correct structure for the regular:
INSERT INTO xyz (col1, col2)
VALUES ('a','b'),('c','d');
I'm getting the data I want to insert from a api endpoint. I'm then looping trough the data to build up the correct structure for the insert.
Can you see how this is confusing me?
The values as I see them in your screenshot are not a text string in the (a,b),(c,d) format, but instead an array of arrays [ [a,b] , [c,d] ].