We help IT Professionals succeed at work.
Get Started

SQL While loop

patd1 asked
Last Modified: 2015-03-31
I have a table with a 1 billion plus records, having fields timeID and UID , RID and some more.
There are multiple UID values associated with Each TimeID. Time ID starts with 164.

I want to run update on all records in smaller subsets to improve update efficiency. so I am doing a while loop such that it updates all records for one UID  for each time id. I have set timeID in the outer loop and UID in the inner loop. To be able to verify process, I am inserting timeID and UID in a table.

When I run this code, it runs all UID (-99, -88, 26 to 53) with time ID 164, which is good, but I am expecting this for all timeID from 164 to 1000. Please check my code and suggest why it is not showing me any other timeID except 164.

DECLARE @counter INT
DECLARE @min_count INT
DECLARE @max_count INT
DECLARE @sub_counter INT
DECLARE @Sub_min_count INT
DECLARE @sub_max_count INT

Truncate table dbo.Status_Update_myTable

-- Query the table to find the starting timeID
SET @min_count = (SELECT Min(timeID) FROM   myTable)
PRINT 'Min timeID = '  + Cast(@min_count AS VARCHAR(8))

-- Query the table again to find the last Time ID
SET @max_count = (SELECT Max(timeID) FROM   myTable)
PRINT 'Max timeID = '  + Cast(@max_count AS VARCHAR(8))
-- Query the table to find the starting UID
SET @Sub_min_count = -99 
PRINT 'Min UID = ' + Cast(@Sub_min_count AS VARCHAR(8))

-- Query the table again to find the last UID
SET @sub_max_count = (SELECT max(UID) FROM   myTable)
PRINT 'Max UID = ' + Cast(@sub_max_count AS VARCHAR(8))

-- Set Loop Control Variable Values
-- Set initial value (Min timeID Value - 1)
SET @counter = @min_count - 1
SET @sub_counter = @Sub_min_count -1

print @counter
print @sub_counter 

WHILE @counter < @max_count + 1 --Loop until counter greater than max Time ID
      IF @counter = 212
        SET @counter = 999
        SET @counter = @counter + 1 --Increment Counter
      PRINT 'Updating timeID: '
            + Cast(@counter AS VARCHAR(8))

      WHILE @sub_counter < @sub_max_count + 1 --Loop until Sub counter greater than max UID
            SET @sub_counter = @sub_counter + 1 --Increment Counter
            PRINT 'Updating UID: '
                  + Cast(@sub_counter AS VARCHAR(8))

            -- Sample logic
           --update statement here

            Select @counter,@sub_counter 
            INSERT INTO dbo.Status_Update_myTable
            (timeId, UID)
            SELECT @counter,@sub_counter  
            if    @sub_counter = -99 Set @sub_counter = -89 
            if    @sub_counter = -88 Set @sub_counter = 25         
           -- END logic

Open in new window

Watch Question
Business Intelligence Developer and Analyst
Top Expert 2015
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE