Re: => Query to copy tbl1 split entries into tbl2

From: Rhonda Fischer (anonymous_at_discussions.microsoft.com)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 02:15:53 -0700

Hello,

Thank you MGFoster for your reference to the SQL Books
On Line, that was very helpful. I am now using a cursor
with a while loop. It is partially working however seems
to have recursive behaviour that means the output is
incorrect.

'****************************************************
INSERT INTO tblOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( 8, 8, 3, '2004-04-13', 5)
INSERT INTO tblOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( 9, 8, 3, '2004-04-13', 9)
INSERT INTO tblOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
VALUES ( 10, 8, 3, '2004-04-13', 18)
'********************************************************

Outputs the following trace statements according to my
print comments in the Trigger:

Load values of first record into local variables
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Close the Cursor

(1 row(s) affected)

Load values of first record into local variables
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Close the Cursor

(1 row(s) affected)

Load values of first record into local variables
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Enter Cursor
Enters LineSplit = 0

(1 row(s) affected)

Loop to next record in the cursor
Close the Cursor

(1 row(s) affected)
'*******************************************************
And produces the following entries in the tblSplitOrders
table. One entry whether less than or greater than
the maximum 26 loads is correctly copied into this table
as soon as more than one entry is inserted problems occur?

DeliveryID CollectionID TempID OrderDate Loads
   8 8 3 13/04/2004 5
   8 8 3 13/04/2004 5
   9 8 3 13/04/2004 9
   8 8 3 13/04/2004 5
   9 8 3 13/04/2004 9
   10 8 3 13/04/2004 18
'*******************************************************
This is the trigger that I am using. It successfully
splits loads greater than 26 into multiples of 26 with the
remaining load. It just doesn't handle more than one
insert effectively. It seems to load the first entry in
the cursor twice... Do you have any ideas where I have
gone wrong, your suggestions are most appreciated.

Thank you kindly

Best Regards
Rhonda

'*******************************************************
CREATE TRIGGER trgLoadExceedTrlCapacity ON [tblOrders]
FOR INSERT
AS
        /* The purpose of this trigger is to ensure that
no entries have in excess of 26 pallet loads */
             /* The Supermarket may order more than 26
loads of produce but the Trailer can only manage 26 loads
*/
             /* so extra lines are inserted into the new
table tblSplitOrders with a maximum of 26 loads */

        Declare @intDeliveryID int, @intCollectionID int,
@intTempID int, @dteOrderDate datetime,
                @intNumOfLoads int, @intLineSplit int,
@intLoadsRemainder int, @intMaxLoads int,
@LoadExcessCursor Cursor

        Set @intMaxLoads = 26

        Set @LoadExcessCursor = Cursor For
                Select DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads,
                        cast(round
(NumOfLoads/@intMaxLoads, 0) as int) as LineSplit,
                        NumOfLoads - (cast(round
(NumOfLoads/@intMaxLoads, 0) as int) * @intMaxLoads) as
LoadsRemainder
                From tblOrders

        Open @LoadExcessCursor

        /*Load values of first record into local variables
*/
        print 'Load values of first record into local
variables'
        Fetch Next From @LoadExcessCursor
        Into @intDeliveryID, @intCollectionID, @intTempID,
@dteOrderDate, @intNumOfLoads, @intLineSplit,
@intLoadsRemainder

        While (@@FETCH_STATUS = 0)
                begin
                print 'Enter Cursor'
                if @intLineSplit = 0
                        begin
                        print 'Enters LineSplit = 0'
                         /* NumOfLoads between 0-26 insert
without splitting order lines*/
                        INSERT INTO tblSplitOrders
(DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
                        VALUES ( @intDeliveryID,
@intCollectionID, @intTempID, @dteOrderDate,
@intNumOfLoads)
                        end
                else
                if @intLineSplit > 0
                        begin
                        print 'Enters LineSplit >0'
                        /* NumOfLoads greater than 26 -
Split Loads to have a maximum of 26 Loads per record */
                        /* loop to Insert split loads */
                        While @intLineSplit >= 1
                                begin
                                print 'Split load by
multiples of 26 - loop and insert here'
                                INSERT INTO
tblSplitOrders (DeliveryID, CollectionID, TempID,
OrderDate, NumOfLoads)
                                VALUES ( @intDeliveryID,
@intCollectionID, @intTempID, @dteOrderDate, @intMaxLoads)
        
                                Set @intLineSplit =
@intLineSplit - 1
                                end
                        /* insert last entry with
NumOfLoads equal to LoadsRemainder */
                        print 'Insert Loads Remainder'
                        INSERT INTO tblSplitOrders
(DeliveryID, CollectionID, TempID, OrderDate, NumOfLoads)
                        VALUES ( @intDeliveryID,
@intCollectionID, @intTempID, @dteOrderDate,
@intLoadsRemainder)
                        end
                print 'Loop to next record in the cursor'
                Fetch Next From @LoadExcessCursor
                Into @intDeliveryID, @intCollectionID,
@intTempID, @dteOrderDate, @intNumOfLoads, @intLineSplit,
@intLoadsRemainder
                end

        print 'Close the Cursor'
        Close @LoadExcessCursor
        Deallocate @LoadExcessCursor

Relevant Pages