Speed up Bulk Upserts and Updates in Retool with MERGE + Staging Tables

[lindakwoo]

If you’re facing the issue of bulk upserts or bulk updates being slow or hitting your DB limits, it’s likely because under the hood, Retool loops row by row through each item in your data set — making a separate DB connection for each row .

To avoid this, you can use a staging table and a single MERGE statement to handle all rows in one fast, efficient query.

Let’s say you have a table of users with:

• id as an auto-generated primary key
• user_id as a unique identifier (like from your auth system)
• name and email as user data

1. Bulk Update (Only update rows that have changed)

Let’s say you want to update emails for existing users — but only if those values actually changed

Steps:

• Create an empty staging table
• Bulk insert your dataset into that staging table
• Use this SQL query to merge with your production data:

MERGE INTO "productionTable" AS p
USING "stagingTable" AS s
ON p.user_id = s.user_id
WHEN MATCHED AND p.email IS DISTINCT FROM s.email THEN  
    UPDATE SET email = s.email

2. Bulk Upsert (Only update changed values + insert new rows)
Now let’s say you want to update emails for existing users — but only if those values actually changed and insert any new users who aren't already in the database.

Steps:

• Create an empty staging table
• Bulk insert your dataset into that staging table
• Use this SQL query to merge with your production data:

MERGE INTO "productionTable" AS p
USING "stagingTable" AS s
ON p.user_id = s.user_id
WHEN MATCHED AND p.email IS DISTINCT FROM s.email THEN
    UPDATE SET email = s.email
WHEN NOT MATCHED THEN
    INSERT (user_id, name, email) VALUES (s.user_id, s.name, s.email);

Why is this better?

• Massively faster: Instead of sending 1,000+ individual queries, you just send ONE
• Smarter updates: IS DISTINCT FROM ensures you only update rows that actually changed — skipping over unchanged rows and reducing write I/O
• All-in-one logic: Insert new rows and update changed ones with a single query — no extra logic needed.
• Scalable: Works seamlessly with large datasets and avoids hitting row or connection limits in Retool or your DB.

[edwardfung123]

I am building a retool app. How to get the data loaded into the stagingTable in the first place with resource query?

[lindakwoo]

@edwardfung123 ,
Create the empty staging table in your retool db. Then, when you have your dataset, do a bulk insert in the resource GUI: