I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.
What are my options for doing this? Is there a simple way to do a "dry run" of a DELETE?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH:
BEGIN TRANSACTION; BEGIN TRY DELETE #1; DELETE #2; DELETE #3; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; END CATCH
If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/CATCH, e.g.
BEGIN TRY DELETE #1; END TRY BEGIN CATCH PRINT 1; END CATCH BEGIN TRY DELETE #2; END TRY BEGIN CATCH PRINT 1; END CATCH
I would like to improve the solution provided by Aaron Bertrand with some code, in case you want to try to add any element of a table, managing the exceptions to ignore fails or also stop the process afters errors.
This one will select the records from the table and then tries to delete them without exceptions:
DECLARE @MaxErrors INT SET @MaxErrors = 5; // Setting 0 will stop process after the first error! SELECT [Id] , ROW_NUMBER() OVER (ORDER BY Id ASC) AS [Index] INTO #DeletingItems FROM myTable DECLARE @Current INT, @Max INT, @Id INT, @TotErrors INT SELECT @Current = 1 , @TotErrors = 0 , @Max = MAX([Index]) FROM #DeletingTable WHILE @Current <= @Max BEGIN SELECT @Id = [Id] FROM #DeletingItems WHERE [Index] = @Index; BEGIN TRANSACTION; BEGIN TRY DELETE FROM myTable WHERE [Id] = @Id; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; SET @TotErrors = @TotErrors + 1; IF @TotErrors > @MaxErrors BREAK; END CATCH SET @Current = @Current + 1; END