How to get the ID from the bulk inserted records


I have a query to perform bulk insert and I'm wondering how to get those data's IDs, so I can use them to create another table.
The use case is to build a table along with its bridge table. The problem is that the ID is generated after the record is inserted into the table and I can't access it from the query result ( doesn't help).

Any suggestion is helpful. Thank you for the time and help!!!

1 Like

Hey @doris.l, happy to help!

How are the IDs assigned in your database (random UUIDS, incrementing, etc.)?

I'm thinking if your database sets ids incrementally, then the newly inserted rows would be the last however many rows. So you could keep track of how many you inserted using a temporary state and then do something like SELECT * FROM table ORDER BY id LIMIT {{state1.value}}.

Hi @Jay,
Thank you for replying to it. The IDs are random UUID so incremental ID solution doesn't apply.

Hey, could you send a screenshot of the left panel of your bulk insert query with the data dropped down as much as possible?

Also, what database type are you using (Postgres, MySQL, API, etc)?

I was looking at how to do this today. It looks like it isn't possible. There may be a bug capturing returning from the sql query, or this just isn't implemented right now. Upon doing a bulk insert, there is nothing useful on the query object except for the number of rows inserted. It's kind of silly that we have to query for the data we just inserted since that's exactly what returning is for.

1 Like

I have the same issue. This something really fundamental. Why isn't it supported in Retool?

1 Like

I wrote a suggestion how to solve this here: Sidestepping Retool's SQL Limitations