error handling.

Question

hi team, im trying to use following logic. im doing a insert into <destination>... select * from <source> in a batch of 10K rows and then truncate source table. However if anything goes wrong with insert into... statement i do not want truncate table to happen. Is my following logic correct. IMO begin try/end try and begin catch/end catch will automatically do that, is my understanding correct.

begin try

step 1. while loop to insert using batch of 20000
insert into dest... select * from source

	--If any error occurs before this below truncate should not execute.  
	  

step 2. truncate table source.

end try
begin catch

end catch.

Answer 1

Yes, that is correct. I also recommend that you add SET XACT_ABORT ON to your script.

Note: when including code in your post, use the button with ones and zeroes on it for a more legible version.

Answer 2

You can use transaction :
https://www.w3resource.com/sql/controlling-transactions.php

It would ensure that everything would be reverted to original state in case of any issues

Answer 3

I want to know if the code i have written will take care of error handling im looking for

Answer 4

I highly recommend you delete the records from the source as you insert them. Otherwise, you will not be able to pick up where you left off. If you run your code again as written, you will get duplicate inserts.

BEGIN TRY  
	DECLARE @rc INT;  
	SET @rc = 20000;  
  
	WHILE @rc = 20000  
	BEGIN  
		BEGIN TRAN  
			-- Insert batch		  
			INSERT INTO dest   
				SELECT TOP 20000 FROM source  
			SET @rc = @@ROWCOUNT  
  
			-- Delete batch from source  
			DELETE FROM source WHERE EXISTS IN dest  
		COMMIT  
	END  
END TRY  
BEGIN CATCH  
	IF @@TRANCOUNT > 0  
		ROLLBACK;  
	THROW;  
END CATCH