T-SQL WHILE LOOP Break Out When Ceiling is Reached Assistance Needed ....

Hi All:

I am trying to do an insert from 1 table to another in increments based on a ceiling of a date field converted to an integer using a T-SQL WHILE...BREAK...CONTINUE Loop.

I've got the code working fine to do the insert but am having difficulty breaking out of the loop once the ceiling condition is reached. It is bringing in all the records instead. Attached is a sample code file. Any assistance is MUCH appreciated. -- Thanks!
WHILE-LOOP-TEST.sql
LVL 9
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:

       SET @counter = @counter
shouzld be:


       SET @counter = @counter +1

no?
0
pcelbaCommented:
The
SET @counter = @counter
is useless command

The question is why do you need the WHILE loop? You may simply insert records at once:

INSERT INTO  [AdventureWorks].[Sales].[BranchSales]
  SELECT  [rowguid], [SalesOrderDetailID], [LineTotal],
               CAST([ModifiedDate] AS int) AS DateOfRecord
    FROM [AdventureWorks].[Sales].[SalesOrderDetail]
  WHERE CAST([ModifiedDate] AS int) < 37863
   ORDER BY [SalesOrderDetailID]

Also the BREAK condition (which is not necessary because the loop is not necessary) should look this way:
IF (SELECT ISNULL(MAX(DateOfRecord),0)FROM [AdventureWorks].[Sales].[BranchSales]) >= 37863
  BREAK
ELSE
  CONTINUE
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
The real Insert into table statement code is actually pulled from a proc that will be called in with an execute rather than the actual code provided ( I included sample code broken out for test purpose only) so unfortunately  I would not have the luxury of adding in the where statement of: WHERE CAST([ModifiedDate] AS int) < 37863 to the proc.
The requirement is to transfer  the records in smaller  increments using the proc insert statement based on the max date of record from Table 1 to Table 2, have the ability to start and stop it at any given time and start back where the last record inserted left off without duplicating.  ....
so I was trying to see if  I could use a while loop & break the loop with a ceiling and set a parameter to that counter to accomplish. it. Is there a way with the while loop to do this and break out of the loop once a celing is hit ... and then start back up where the last batch left off? --- Thanks!
 
 
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pcelbaCommented:
If you have no luxury to use WHERE clause then how could you tell the procedure "Don't return records which I already have from previous batch" ? How do you tell the proc "don't return records having date > maxdate"?

You cannot use just the max date but min date also to define the starting point of the batch to avoid duplicities on proc output.

You should post some code sample which is closer to your real environment. Then we may help much better.
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
Thank You hence my dilema on this. I am not allowed to post client code from my environment to a public site. The sample code that I provided is as close to the type of data I can provide.
So using the AW Sales Detail Table to transfer (Insert)  the data ( as a proc)  to a newly created  Sales Branch Table  casting the Modified Date to a integer (with either a MIN or Max) is all I have to work with for the concept. for this.
The requirement is to transfer  the records in smaller increments (using an existing insert table stored procedure that cannot be changed) based on the ( *** min or max date is just fine to use however I can)  from Table 1 to Table 2, have the ability to start and stop it at any given time and start back where the last record inserted left off without duplicating.
With the minimum date and maximum date in mind then is it possible with the While Loop Break Out when reaching a ceiling based on a MIN. & MAX added or changed in here somewhere? -- Attaching modified code where I added in a ceiling parameter ... Any help is much appreciated.

WHILE-LOOP-TEST-WITH-MODIFICATIO.sql
0
pcelbaCommented:
I still don't understand how would you like to "break the loop" if there is no loop necessary?

So, if you are able to call the SP with Min and Max value defined then you don't need any loop. You'll simply define Min and Max value, call the SP and the SP will do all inserts for you.

SET @Min = 5  -- this value can be calculated, of course
SET @Max = 10  -- or calculation

EXEC sp_insert @Min, @Max

If you cannot disclose your code then I don't care about it BUT you should be able to tell how it is called, what parameters it needs and what values it returns.

If you need the loop (to call SP with certain parameters):

SET @Min = 5  -- this value can be calculated, of course
SET @Max = 10  -- or calculation

DO WHILE 1=1
BEGIN  
  EXEC sp_insert @Min, @Max
  SET @Min = @Max + 1
  SET @Max = ... some calc
  IF @Max = ... something
    BREAK
END
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
Oh  my Thank You for this. Well I  now see the confusion between what I am asking and what you sent now.
That is just the issue, the existing  proc is not set up for a min or max input parameter it only does a bulk insert and I am tasked with using that proc "as is" (without any ability to pass in any parameters to it) .  I am not allowed to change this beginning proc. This is why I incluided just the insert code in the sample and there were no parameters mentioned because there are none.
I have to take the output bulk insert statement from what is produced from that beginning proc and populate a new table in increments with the above mentioned stopping, starting and non duplicating stipulations.
This is why I was trying to use the While Loop with a ceiling  break somehow.
Thanks Again.
0
pcelbaCommented:
So, does it mean you'll always obtain the whole set of records but you have to insert records which are not inserted yet? And also you have to split the insert command into several smaller batches?
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
Yes That's It Exactly :)
0
pcelbaCommented:
OK, then you are able to use WHERE clause to filter inserted records...

And you have to check the rowguid for unicity because the DateOfRecord is not reliable. Another help would give you ROW_NUMBER function which allows easier batch definition:

DECLARE @counter INT, @ceiling INT, @batchSize INT
SET  @counter =(SELECT ISNULL(MAX(DateOfRecord),0)FROM [AdventureWorks].[Sales].[BranchSales])
SET  @ceiling =(SELECT MAX(CAST([ModifiedDate] AS int))FROM [AdventureWorks].[Sales].[SalesOrderDetail])
SET @batchSize = 10

-- here you have to create the whole set of records

WHILE 1=1
BEGIN
  INSERT INTO [AdventureWorks].[Sales].[BranchSales]
    SELECT [rowguid],[SalesOrderDetailID],[LineTotal], 
           CAST([ModifiedDate] AS int)AS DateOfRecord
      FROM (
      SELECT [rowguid],[SalesOrderDetailID],[LineTotal], 
             CAST([ModifiedDate] AS int)AS DateOfRecord,
             ROW_NUMBER() OVER(ORDER BY [ModifiedDate]) rn
        FROM [AdventureWorks].[Sales].[SalesOrderDetail]
       WHERE [rowguid] NOT IN (SELECT [rowguid] FROM [AdventureWorks].[Sales].[BranchSales])
           ) x
    WHERE rn <= @batchSize
  
  IF SomeBreakCondition
    BREAK
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
Thank You for this, great coding and I can see other uses for it, but I'm not sure how to use it for just this situation.... you say I have to create the whole set of records? --- Can the existing stored procedure be used to create the wholeset of records somehow?
 
 
0
pcelbaCommented:
I am saying "create the whole set of records" because your procedure does not accept parameters, so the records retrieved by it are always the "whole set".

If you are able to obtain less number of records then you may process them in the same loop.
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
Thank You but again I cannot change the existing stored procedure to add in the lines like
ROW_NUMBER() OVER(ORDER BY [ModifiedDate])
or  
WHERE [rowguid] NOT IN (SELECT [rowguid] FROM [AdventureWorks].[Sales].[BranchSales])
I have to use it "as is".
All I am able to do is execute the existing stored procedure to fill the new table as it stands.
Thanks very much  for all your help but I am not able to do it this way.
 
0
pcelbaCommented:
OK, then you have to do it the way described in answer ID:30451730:

1) Retrieve all records from SP to [AdventureWorks].[Sales].[SalesOrderDetail] table (or some temp table)
2) Process above table in batch
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
OK I see thanks. I will try it that way in the next week and see how it goes. Thanks again for your help.
0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
Thank You very much. The combination of the two solutions is just what I needed!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.