Handle partial failures in a transaction (commit selectively) in MSSQL
1
vote
1
answer
471
views
I am doing a batch update where I process records sequentially (User 1, User 2 etc). Each record has multiple update queries associated to it. I need to skip if there is a data issue for an individual record (user in this case). Ideally, I would like to process them in parallel, but haven't reached that level yet (lots of challenges).
Is it possible to do it such that:
1. There is a single transaction.
2. If there is a failure the statements associated with **that** record gets rolled back, **without** affecting others.
3. Commit.
For example, I have 4 users in a CSV file. If 3 are good and 1 is bad, 3 should get committed (or aborted) atomically; 1 should get skipped with errors.
Observed:
~~~
do everything for user 1;
do everything for user 2;
--> if there is failure, it rolls back the *entire* transaction
do everything for user 3;
~~~
In fact, any error level >= 16 is rolling back the entire transaction.
Expected:
~~~
do everything for user 1;
do everything for user 2;
--> if there is failure, roll back this *block* only
do everything for user 3;
do everything for user 4;
~~~
It's a normal
try-catch
requirement in any programming language; however, couldn't see a SQL Server equivalent (involving transactions). I have read about checkpoints, but not sure if that's an option to consider.
Asked by Nishant
(899 rep)
Oct 23, 2023, 03:07 PM
Last activity: Oct 26, 2023, 02:51 AM
Last activity: Oct 26, 2023, 02:51 AM