While loop for batch insert

Transact-SQL
1

Hi:
Could any one tell me why the loop doesn't stop running, even though the count exceeds the condition - here I am trying to insert data in batches . Thanks

 DECLARE @batch_size  INT,

 @row_counter  INT,

 @maxrow_count INT;



SET @batch_size      = 5000; -- batch of 5000

 SET    @row_counter= 1;



SELECT @maxrow_count = max(RNO) FROM #DATA

  PRINT @maxrow_count

WHILE @row_counter <= @maxrow_count

 BEGIN 

    INSERT INTO DBO.PrevPriceCheck (SecurityID,Ticker,Cusip ,Price_Date,Price_final,PrvPriceDate )

   SELECT a.securityid,a.ticker,
    a.cusip,a.price_date
  ,a.price_final,MAX(b.PriceDate) PrvPriceDate --into                                    DBO.PrevPriceCheck

 FROM  #DATA a WITH(NOLOCK)

 LEFT JOIN ad_08142015 b WITH(NOLOCK)ON a.SecurityId= b.SecurityID

                                                                 and a.price_date > b.PriceDate

                                                                 and b.DataId = 7





AND A.RNO between @row_counter and (@row_counter + @batch_size)

  GROUP BY  a.ticker,a.cusip,a.price_date,a.price_final,a.securityid

-- Set the @row_counter to the next batch start

      SET @row_counter = @row_counter + @batch_size + 1;


  END
2

Add PRINT @row_counter after the SET (before the END) to see what value it gets up to. Otherwise, please format your code so that we can copy/paste. Right now some of it is in a code block and some of it isn't.

3

Hi;
Hmm I tried tried that , and the counter gets incremented after a long time and the data gets inserted twice... Is there any mistake in the script ? Thanks

4

Okay shall do that

5

I think you need to remove the +1 from the SET.

6

Okay ... Will do that..

7

I think the problem is that your @row_counter test is in the OUTER JOIN

AND A.RNO between @row_counter and (@row_counter + @batch_size)

so is only restricting the rows from ad_08142015,it will still select/insert every row from #DATA every loop iteration

Why are you using NOLOCK? Very dangerous in this type of situation.

1 Like
8

Hi Kirsten,

I just need the previous max date from table ad_08142015,i.e. the previous date just before the date in the temperory table #data.

9

Hi-
I tried that by removing; Its just looping

10

hI -

The below is the O/P message that i get .....It goes on inserting.....The Row count in #Data is 522423 ...and it seems that the row counter is exceeding 5000 -the batch size-

1
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
5001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
10001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
15001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
20001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
25001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
30001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
35001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
40001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
45001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
50001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
55001
Warning: Null value is eliminated by an aggregate or other SET operation.

(522423 row(s) affected)
60001
Warning: Null value is eliminated by an aggregate or other SET operation.

11

The problem with additional conditions involved in OUTER JOIN

There is a difference(result set) between adding additional conditions in ON clause of LEFT OUTER JOIN
and where clause. If you want to restrict to only batch size (5000) keep below condition in where clause

WHERE A.RNO between @row_counter and (@row_counter + @batch_size)

1 Like
12

Hi Kristen:-
Your finding was correct the @rowcounter test was in the outer join
Thank you. :smile:

13

Thanks Krisna:-
Got it :slight_smile:

14

:slight_smile: glad you got it fixed.

Now ... what about that NOLOCK eh? :smile:

2 Likes