



CREATE PROCEDURE DBO.TempCopyPackets AS
SET NOCOUNT ON
/*
Between 2 db's
*/

DECLARE
@ProductNumber		INT,
@RoomTypeNumber		INT,
@DateStamp			CHAR(8),
@ArrivalPacketNumber		INT,
@StayPacketNumber		INT,
@DeparturePacketNumber	INT,
@Transfered			INT

SET @Transfered = 0

DECLARE #ProductPriceCur CURSOR FAST_FORWARD FOR

  SELECT ProductNumber, RoomTypeNumber, DateStamp, ArrivalPacketNumber, StayPacketNumber, DeparturePacketNumber FROM [jptest]..ProductPrice WITH (NOLOCK)
  WHERE ArrivalPacketNumber > 0 OR StayPacketNumber > 0 OR DeparturePacketNumber > 0

OPEN #ProductPriceCur

FETCH NEXT FROM #ProductPriceCur
INTO @ProductNumber, @RoomTypeNumber, @DateStamp, @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @@FETCH_STATUS <> -2
  BEGIN
     UPDATE ProductPrice SET ArrivalPacketNumber=@ArrivalPacketNumber
     WHERE ProductNumber=@ProductNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp AND ArrivalPacketNumber<>@ArrivalPacketNumber AND ArrivalPacketNumber IS NULL
     IF @@rowcount = 1
       SET @Transfered=@Transfered + 1

     UPDATE ProductPrice SET StayPacketNumber=@StayPacketNumber
     WHERE ProductNumber=@ProductNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp AND StayPacketNumber<>@StayPacketNumber AND StayPacketNumber IS NULL
     IF @@rowcount = 1
       SET @Transfered=@Transfered + 1

     UPDATE ProductPrice SET DeparturePacketNumber=@DeparturePacketNumber
     WHERE ProductNumber=@ProductNumber AND RoomTypeNumber=@RoomTypeNumber AND DateStamp=@DateStamp AND DeparturePacketNumber<>@DeparturePacketNumber AND DeparturePacketNumber IS NULL
     IF @@rowcount = 1
       SET @Transfered=@Transfered + 1
  END

  FETCH NEXT FROM #ProductPriceCur
  INTO @ProductNumber, @RoomTypeNumber, @DateStamp, @ArrivalPacketNumber, @StayPacketNumber, @DeparturePacketNumber
END

CLOSE #ProductPriceCur
DEALLOCATE #ProductPriceCur

PRINT 'Transfered packets:'
PRINT @Transfered

