How can I apply the loop statement into the temp table in sql? -

i have following code run loop insert date rage tamp table, , working fine:

;with testone([ddate], leave) (     select convert(date, '2013-05-06') 'ddate', 1    union    select dateadd(dd,1,ad.ddate), 1    testone ad    ad.ddate < convert(date, '2013-05-08')  ) 

the result :

adate       leave 2013-05-06  1 2013-05-07  1 2013-05-08  1 

however, when trying insert records tamp table following, it's seem not working me:

create table #testtwo  (     ddate date,     leave int  )    insert #testtwo          ( ddate, leave ) select convert(date, '2013-05-06') 'ddate', 1 union select dateadd(dd,1,ad.ddate), 1 #testtwo ad ad.ddate < convert(date, '2013-05-08')   

the result :

adate       leave 2013-05-06  1 

any way can insert same record 1st code 2nd code using tamp table #test?

your first example special construct called "recursive common table expression". not temporary table, special kind of query can recursively (or, iteratively) refer own output.

as far i'm aware, way in pure sql (i.e. not embedded procedural language) achieve recursion of kind.

what don't show using recursive cte anything. if want insert result table, need carry on using cte, insert statement. i'm not sure dbms you're using, can't confirm exact syntax, should this:

with testone(ddate, leave) (     select convert(date, '2013-05-06'), 1    union    select dateadd(dd,1,ad.ddate), 1    testone ad    ad.ddate < convert(date, '2013-05-08')  ) insert #testtwo ( ddate, leave ) select ddate, leave testone