user1205746 user1205746 - 9 months ago 29
SQL Question

Save data of a common table expression into a table

This is an extension of my previous question:
Auto generating dates based on a table

I got the resolution which works perfectly

with n as (
select row_number() over (order by (select null)) - 1 as n
from master..spt_values
)
select t.*, dateadd(day, n.n, t.startDate) as thedate
from t join
n
on dateadd(day, n.n, t.startDate) <= t.endDate;


However, I would like to make the result persistent by saving it into a table. Is it possible to persistent the data? I tried the select into statement but it did not work

The whole statement is:

select into ABC
(
with n as (
select row_number() over (order by (select null)) - 1 as n
from master..spt_values
)
select t.*, dateadd(day, n.n, t.startDate) as thedate
from t join
n
on dateadd(day, n.n, t.startDate) <= t.endDate
)


The error received was:

Msg 156, Level 15, State 1, Line 146
Incorrect syntax near the keyword 'into'.
Msg 319, Level 15, State 1, Line 148
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 168
Incorrect syntax near ')'.


How do I save the result into a table?

Answer Source

Notice where the into ABC is

 ;with n as (
    select row_number() over (order by (select null)) - 1 as n
    from master..spt_values
)
select t.*
     , dateadd(day, n.n, t.startDate) as thedate
 into ABC
 from t join  n      
   on dateadd(day, n.n, t.startDate) <= t.endDate