none
My batch bulk update execute only one time RRS feed

  • Question

  • Dear all,

    I have the following update querry which execute only the 10 first records and stop:

    SET @results = 1
    SET @batchSize = 10
    SET @id_control = 0
    
    BEGIN TRY    
      WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		
    		  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'é','é') AS NText) WHERE BODY LIKE '%filename=%è%'
    		  
    		  
    		 
    		  SET @results = @@ROWCOUNT;
    		  IF @@ROWCOUNT < @BatchSize
    			BEGIN
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Count rows :' + @count;
              COMMIT TRANSACTION   
    	  
    	  -- next batch
    	  --SET @id_control = @id_control + @batchSize
    
      END;
    END TRY
    BEGIN CATCH
      RAISERROR('fail to update record :',1,1,'');
      RETURN;
    END CATCH;

    I have more than 10 records in the database matching like  criteria, any idea why it is stoping executing after the first 10 records ?

    thanks for help

    regards

    Tuesday, September 10, 2019 2:50 PM

Answers

  • When you want to check @@rowcount, you should always capture @@rowcount in a local variable directly after the statement you want to check and then use that variable.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by wakefun Thursday, September 12, 2019 9:13 AM
    Thursday, September 12, 2019 9:09 AM

All replies

  • Hi wakefun,

    the problem is with this statement:

    PRINT N'Updated Count rows :' + @count;

    Change this to

    PRINT N'Updated Count rows :' + CAST(@count as varchar(50));


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit


    Tuesday, September 10, 2019 3:16 PM
  • I manage to identify the issue but no reason why it behaves like that.

    In order to break the loop I am testing @@RowCount < @BatchSize and it jump into that code block with condition true.

    But when I check the @result =@@rowcount  is equal to 10 and bacthsize is 10 by default

    So why the condition is true ?

    regards

    Tuesday, September 10, 2019 3:20 PM
  • I manage to identify the issue but no reason why it behaves like that.

    In order to break the loop I am testing @@RowCount < @BatchSize and it jump into that code block with condition true.

    But when I check the @result =@@rowcount  is equal to 10 and bacthsize is 10 by default

    So why the condition is true ?

    Because @@rowcount holds the number of values affected by the most recently executed statement.

    Which in the case of the statement

       IF @@ROWCOUNT < @BatchSize

    is the statement

       SET @results = @@ROWCOUNT;

    Which only affects one row.

    Here is a quick demo:

    SELECT TOP (10) *  FROM sys.objects
    SELECT @@rowcount
    SELECT @@rowcount


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 10, 2019 10:01 PM
  • Hi wakefun,

    Erland's right, I just read a blog which can also explain it: 

    Using @@ROWCOUNT can be a bit tricky

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 11, 2019 7:31 AM
  • Thnaks for your answer Erland,

    so what will be the correctn test to perform to avoid this behaviour with @@rowcount ?

    thanks

    Thursday, September 12, 2019 7:37 AM
  • When you want to check @@rowcount, you should always capture @@rowcount in a local variable directly after the statement you want to check and then use that variable.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by wakefun Thursday, September 12, 2019 9:13 AM
    Thursday, September 12, 2019 9:09 AM