Csv to temp table in SQL Server query

,

I'd like to be able to upload a .csv file, see the contents of that file in a table component, and then load the contents of that file into a temp table that I can use in a SQL query.

I'm only able to get one row (selectedRow) into there.
How do I go about getting all the rows into my temp table?

Here is what I have right now. It seems like this is something I should be able to do, since I can do this much, but I don't know what to even search for to find it in the documentation. I've been trying for a while and am getting nowhere.

Thanks.

Hi @Pina, welcome to the community :wave:

Do you want to be able to select the IDs you want or put everything displayed in SQL query?
For the former, you need to enable select multiple rows for the table component -> check the data structure of your table component for selectedRow (open left panel) -> then use that in your SELECT statement.

For the latter, you can just simply use displayedData property (i.e. table1.displayedData.personid) as that would give you the entire list of ids displayed in the table.

Thank you. I want to put all of the values from the csv into the temp table. I tried the latter but then the select shows no rows. I also tried it both with and without that Enable batch querying box checked (I don't know what that does.)

image

Hey @Pina, try something like this:

...
INSERT INTO #personid (personid)
SELECT *
FROM {{table1.displayedData.personid}}
...

If you are not selecting anything in the table and merely checking it, you can even use the data from the query you plugged in to your table.

:frowning:
image

DROP TABLE IF EXISTS #personid
CREATE TABLE #personid AS
  SELECT personid
  FROM {{table1.displayedData}}

my bad, shouldn't be calling the personid there.

Same error doing that.
image

I did find another way of doing it that accomplishes the task for a simple, short, one column table.

But, it doesn't work if I want more columns and frankly, it's just an awful way to do it.
Surely there has to be some sort of JavaScript thing that will do what I need it to do.

Hey @Pina, Retool Support here happy to help!

Have you tried using GUI mode which would allow you to bulk insert an array of objects:

This option looks to work with multiple columns:

Hi, thank you! What I want to do is bulk insert them into a temp table that I would use in a query though, not an actual table.

Could temporary state work for this use case? Temp state should help, so long as you pass in an array of objects.