sql - CTE loop in loop -

this question exact duplicate of:

• cte looping query 1 answer

situation: got 2 tables. 1 there 2 fields startdate & enddate. , 1 table there 1 field date. when got 3 days between start- , enddate. must insert 3 rows in new table.

i have next code , insert perfect line in table availability.

with view_solidnet_training ( select  cast('2013-04-09' datetime) datevalue union select datevalue + 1 view_solidnet_training datevalue + 1 <= cast('2013-04-11' datetime) ) insert obj_availability   select 34, datevalue, 'am', 2, 'test' view_solidnet_training; 

but now, after inserts lines in new table, stops. after loop, must change the start , enddate again in new values of next row in view: view_solidnet_training.

so there possible solution, or should make new loop check if id of view not zero?

as understand question think should start getting dates list , insert.

sample:

create table dates ( startdate datetime, enddate datetime )  insert dates select '2013-04-06','2013-04-08'  select * dates  declare @date int declare  @tbl table  ( date_ datetime )  select @date = datediff(day,startdate-1,enddate) dates select @date  while(@date != 0 ) begin   insert @tbl select dateadd(day,@date,startdate-1) dates  set @date = @date -1  end  /* --to-do --update startdate , enddate values in table dates -- insert yourtable  -- select date_ @tbl */ select * @tbl order date_