SQL code to bulk update

Queries and Resources
1

Hello, very new - been using Retool for 3 days so I am very new to this.

Use case is I have a file import in retool (working) that contains URL's into an object called FileStaging. I am trying to bulk insert into a table that in Google is one row per array row. My database resource is in the google cloud.

My code is:

DECLARE @intVar int = 0;
DECLARE @URL VARCHAR(255);

WHILE @intVar < {{FileStaging.data.length}};
BEGIN
    
    SET @URL = {{FileStaging.data[@intVar]['File Name']}};

    INSERT INTO `config_panel.files_to_ingest`
         (client_UUID,contract_UUID,URL_path)
    VALUES
         (ClientTable.selectedRow.UUID,ContractTable.selectedRow.UUID,@URL);

    SET @intVar = @intVar + 1;
END

The SET fails because the intVar cannot be passed as a parameter...

Ideas are much appreciated!!!! Thanks,

2

Hey @rileydog! Welcome to Retool :slightly_smiling_face:

Would something like this be close to what you’re looking for? http://community.retool.com/t/how-to-run-a-rest-api-query-for-each-item-in-an-array-and-return-all-results-together/2352

Let me know! Happy to help you get set up :slight_smile:

3

Thanks so much for the reply! Not sure how I can use that tool for my particular issue.

Maybe there is a better way to go about how to solve my problem:

  • I have a csv file with only one column, a URL
  • I have a table that imports that data (works well) into retool
  • I want to import all these rows in bulk to the destination SQL table in google - the examples I can find only show one insert at a time (a button at the row level to upload that row)
  • the destination SQL table requires 3 columns to do an insert -- I have the other 2 elements I need based on the selected row in another table, with the third being the URL
  • so the issue is, I have a table of URL's and for each URL row, I need to send the other 2 elements (just a field, not an array) to perform a valid insert on that table

Thanks!!!

4

I found a way to do this! I read docs on how to trigger a query for every row and change the value - works great.

1 Like
5

Amazing! So glad to hear it's working for you. How are the rest of your steps going for you? :slight_smile: Let me know how I can help!