How to get the ID from the bulk inserted records

Queries and Resources
1

Hi!

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 ( bulkQuery.data.result doesn't help).

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

1 Like
2

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}}.

3

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

4

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

772×128 6.32 KB

666×1558 63.4 KB

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

5

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.

2 Likes
6

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

2 Likes
7

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

8

This is not really an optimal solution since it can be prone to errors, but it's a workaround that I implemented for the time being. I'm making use of the created_at column and I'm creating another query that gets call on the success of the query.

SELECT
  id
FROM table
WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '15 seconds'
  AND group_id = {{ currentGroup.value.id }};

The group_id works as a second safety net in my case to prevent any other intruder. And many other necessary safety nets can be added.

If there are two users and both of them make a bulk update around the same time to the same group_id, then, there would be a duplicate.

But it is a solution I'm also struggling with the same issue but I hope this is helpful for someone else in the meantime.

Edit: There can be another column of created by and putting in the user_id in the tables where bulk_updates are utilized and that would bypass the duplication case entirely.

1 Like