error handling.

Heisenberg 221 Reputation points
2022-06-29T15:56:05.257+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,517 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,071 Reputation points MVP
    2022-06-29T21:32:12.91+00:00

    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.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Nandan Hegde 29,471 Reputation points MVP
    2022-06-29T16:53:36.767+00:00

    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

    0 comments No comments

  2. Heisenberg 221 Reputation points
    2022-06-29T19:15:42.507+00:00

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

    0 comments No comments

  3. Tom Phillips 17,711 Reputation points
    2022-07-01T12:55:12.757+00:00

    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  
      
    
    0 comments No comments