CODEWITHSUNDEEP
sqlsql-servertemp-tables
Chappy
Chappy

Reputation: 9

SQL: How to create a temp table and fill it with date within a select-from statement

I wish to create a temp table with 1 datetime column and then fill it with date(30 days before today). I wish to do all these in a select-from statement.

I could do it with a "WITH" loop as below prior to the select-from statement. However, I wish to do it within a select-from statement.

declare @endDate datetime
set @endDate = dateadd(day,-30,getdate())

with CTE_Table (
    Select dataDate = dateadd(day,-1,getdate()) from CTE_Table
    where datediff(day,dataDate,@endDate) < 0
)

select * from CTE_Table

Please help... :....(

Upvotes: 0

Views: 12069

Answers (2)

Martin Smith
Martin Smith

Reputation: 453243

You can use SELECT ... INTO.

BTW Your recursive CTE is invalid. A fixed version is below

DECLARE @endDate DATETIME

SET @endDate = dateadd(day, -30, getdate());

WITH CTE_Table(dataDate)
     AS (SELECT  dateadd(day, -1, getdate())
         UNION ALL
         SELECT dateadd(day, -1, dataDate)
         FROM   CTE_Table
         WHERE  datediff(day, dataDate, @endDate) < 0)
SELECT dataDate
INTO   #T
FROM   CTE_Table

Upvotes: 1

Darren
Darren

Reputation: 70728

You could do:

CREATE TABLE #temptable
(
        DateColumn DATETIME
)

INSERT INTO #temptable
SELECT dataDate FROM CTE_Table

Upvotes: 0

Related Questions