Tuesday, July 21, 2015

Copy SQL Table With Child Table Relaionships

dbo.RoomOccupancyRate has many dbo.OccupancyChildRate records.

How to copy them?

Following stored procedure takes care of it. It even has the option to copy only the active items.

ALTER PROCEDURE [dbo].[sp_CopyHotelContractRoomOccupancyRate]
    @NewHotelContractID int,
    @SelectedHotelContractID int,
    @UserName nvarchar(20),
    @ActiveItemsOnly bit,
    @OldDateBandId int,
    @NewDateBandId int,
    @OldRoomOccupancyId int,
    @NewRoomOccupancyId int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements. 
    SET NOCOUNT ON;

    IF (EXISTS(SELECT TOP 1 cr.[RoomOccupancyRateID]
                 FROM [dbo].[RoomOccupancyRate] cr  
           INNER JOIN [dbo].[HotelRoomOccupancy] rc ON cr.[RoomOccupancyID] = rc.[RoomOccupancyID]
                WHERE rc.[RoomOccupancyID] = @OldRoomOccupancyId)) 
    BEGIN
        PRINT '----[dbo].[RoomOccupancyRate] table should be copied'

        DECLARE @_existingRoomOccupancyRateId int, 
                @_newRoomOccupancyRateId int;

        SELECT @_existingRoomOccupancyRateId = MIN([RoomOccupancyRateID])
          FROM [dbo].[RoomOccupancyRate] 
         WHERE RoomOccupancyID = @OldRoomOccupancyId;

            WHILE (@_existingRoomOccupancyRateId IS NOT NULL)
            BEGIN 
                -- insert new record 
                 INSERT INTO [dbo].[RoomOccupancyRate]
                               ([RoomOccupancyID],[ChargeTypeID] 
                               ,[Active],[LastChangedBy],[LastChangedTime]) 
                        SELECT @NewRoomOccupancyId,[ChargeTypeID],[Active],@UserName,GETDATE() 
                      FROM [dbo].[RoomOccupancyRate]
                     WHERE [RoomOccupancyRateID] = @_existingRoomOccupancyRateId 
                       AND CASE
                             WHEN  @ActiveItemsOnly = 1 AND (Active = 1) THEN 1 
                             WHEN  @ActiveItemsOnly = 0 AND (Active = 1 OR Active = 0) THEN 1
                           END = 1

                -- if room occupancy rate record successfully inserted.
                IF(@@ROWCOUNT > 0)
                BEGIN 
                    SELECT @_newRoomOccupancyRateId = @@IDENTITY;

                    PRINT '     |-----[dbo].[OccupancyChildRate] table should be copied'

                    -- copy occupancy child rate table
                    INSERT INTO [dbo].[OccupancyChildRate]
                               ([RoomOccupancyRateID],[MinAge],[MaxAge],[BuyRate],[Active],[LastChangedBy],[LastChangedTime])
                        SELECT @_newRoomOccupancyRateId,[MinAge],[MaxAge],[BuyRate],[Active],@UserName,GETDATE() 
                         FROM [dbo].[OccupancyChildRate] 
                        WHERE [RoomOccupancyRateID] = @_existingRoomOccupancyRateId  
                            AND CASE
                                 WHEN  @ActiveItemsOnly = 1 AND (Active = 1) THEN 1 
                                 WHEN  @ActiveItemsOnly = 0 AND (Active = 1 OR Active = 0) THEN 1
                               END = 1;  
                END

                SELECT @_existingRoomOccupancyRateId = MIN([RoomOccupancyRateID])
                  FROM [dbo].[RoomOccupancyRate]
                 WHERE RoomOccupancyID = @OldRoomOccupancyId
                   AND [RoomOccupancyRateID] > @_existingRoomOccupancyRateId;
            END  
    END 
END