Hi
I am trying to use a loop to insert a date and a value into a table. The idea being that I loop through the date range and update the table if the date already exists (which also includes the day of the week) but insert if it doesn't exists.
For some reason it will only allow be to insert/update 57 records.
The end product will also update some other tables that relate to the same info, but I have only supplied the basic stuff here.
below is the code with an example:
SET NOCOUNT OFF
DECLARE @from_date datetime, @to_date datetime, @room_type_id Int, @rate money
DECLARE @days Int, @counter Int, @id Int
SET @from_date = '2006/01/01'
SET @to_date = '2006/12/30'
SET @room_type_id = 280
SET @rate = $100
SET @days = datediff(day,@from_date, @to_date)
SET @counter = 0
WHILE (@counter < @days)
BEGIN
If EXISTS (SELECT ROOM_RATE_ID FROM ar_room_rate WHERE (ref_date = dateadd(d, @counter, @from_date) AND room_type_id = @room_type_id AND DATEPART(dw,ref_date) IN (1,2,3,4,5,6,7)))
BEGIN
UPDATE ar_room_rate SET rate = @rate WHERE (ROOM_RATE_ID = (SELECT ROOM_RATE_ID FROM ar_room_rate WHERE (ref_date = dateadd(d, @counter, @from_date) AND room_type_id = @room_type_id AND DATEPART(dw,ref_date) IN (1,2,3,4,5,6,7))))
--SELECT 'UPDATE ', dateadd(d, @counter, @from_date)
END
ELSE
BEGIN
IF DATEPART(dw, DATEADD(d, @counter, @from_date)) IN (1,2,3,4,5,6,7)
BEGIN
INSERT INTO ar_room_rate (room_type_id, ref_date, rate)
VALUES
(@room_type_id, DATEADD(d, @counter, @from_date), @rate)
SELECT @id = SCOPE_IDENTITY()
INSERT INTO ar_release_link (release_plan_id, room_rate_id, allotment, adjustment)
SELECT RELEASE_PLAN_ID, @id, default_allotment, default_adjustment FROM ar_release_plan WHERE (room_type_id = @room_type_id)
--SELECT 'INSERT ', dateadd(d, @counter, @from_date)
END
END
SET @counter = (@counter + 1)
END
SET NOCOUNT ON
Start Free Trial
