Insert queries - skip failing records, or rollback transactions

Queries and Resources
,
1

Context:

  • I have a multi-step form. When submitted, it calls a sequence of queries that inserts/updates different tables. The queries are dependent with each other (query 1 needs to finish before query 2, and then query 3 runs next, and so on) so it's important that all of them succeed.
  • Ideally, if any of the subsequent queries fail, the first queries should have to rollback committed transactions, as the user will have to (possibly) update some fields in the form and resubmit it, causing the sequence of queries to be called again.
  • I have a bulk insert query in SQL mode that inserts an array of records to a MSSQL table. Because we are still testing and currently using a dev/test database, there are some test/erroneous data in this db. The query failed because there was one record that was already existing in the db, causing the entire query to fail, and the subsequent queries not getting called anymore.*

Questions:

  1. do bulk insert queries in GUI mode fail if there is one (or possibly more) set of record that fails to insert? If this is the case, is there a way to skip the failing records, and continue inserting the rest of the records?
  2. Is there a way to rollback/undo previous successful queries? For example, if Query 1 succeeded, and Query 2 fails, how can I 'cleanly'** undo the records created/updated by Query 1?

Appreciate any suggestions on workarounds!

* I get that this specific scenario is a data integrity issue but I think it would be good to handle this issue for whatever error subsequent queries run into (timeouts, for example.)
** Cleanly as in not having to define a Delete/Update query to manually delete/update the previously inserted/updated row/s.

2

Hey @lazymaplekoi!

do bulk insert queries in GUI mode fail if there is one (or possibly more) set of record that fails to insert? If this is the case, is there a way to skip the failing records, and continue inserting the rest of the records?

Yes, the bulk insert will fail if any of the records fails to insert successfully. There is not a way to skip failing records and continue to inserts on the subsequent values.

Is there a way to rollback/undo previous successful queries? For example, if Query 1 succeeded, and Query 2 fails, how can I 'cleanly'** undo the records created/updated by Query 1?

Cleanly as in not having to define a Delete/Update query to manually delete/update the previously inserted/updated row/s.

Unfortunately not. You aren't able to create transactions across multiple Retool queries. The best way to manage this is likely to have a JS query that triggers your DB queries and uses the onSuccess and onFailure options to trigger queries that either move forward in the process or revert the previous step(s). There is an example of syntax to do that here.