How to wrap the following CTE so that I can use its output with an INSERT INTO statement?

Question

I am using SQL Server 2012 and I have the following T-SQL query which works fine:

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT 1 FROM N,N a,N b,N c,N d)
SELECT
       Mkt,
       Property,
       Season,
       b.Date,
       TName,

FROM Table1 a
CROSS APPLY
( 
  SELECT top(datediff(d,Datefrom,case when DateTo >= DateFrom
             then dateadd(d, 1, DateTo) else DateFrom end))
    DATEADD(d,row_number()over(order by 1/0)-1, DateFrom) Date
  FROM tally
) b

I need to insert the output of the above query into another Table (let's call it Table2).

How do I wrap the above query with the INSERT INTO syntax?

Answer 1

You need to use like below.. Check this line..INSERT INTO yourTableName(col1,col2.....) Define your tablename and columnnames..

;WITH N(N)AS 
(
    SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)
),
tally(N) AS
(
    SELECT 1 FROM N,N a,N b,N c,N d
)
INSERT INTO yourTableName(col1,col2.....)
SELECT
       Mkt,
       Property,
       Season,
       b.Date,
       TName,

FROM Table1 a
CROSS APPLY
( 
  SELECT top(datediff(d,Datefrom,case when DateTo >= DateFrom
             then dateadd(d, 1, DateTo) else DateFrom end))
    DATEADD(d,row_number()over(order by 1/0)-1, DateFrom) Date
  FROM tally
) b