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 keyuser_id
as a unique identifier (like from your auth system)name
andemail
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.