生成日期记录表的5种方法

原文：http://sqlblogcasts.com/blogs/tomaztsql/archive/2011/07/04/datetime-tally-table.aspx

 

生成日期记录表的5种方法（尽管有点像孔乙己的茴字有五种写法，不过对学习t-sql还是很有帮助）:

-- 方法1.

;create table tc..dt
(id int identity(1,1)
,dt datetime)

insert into dt(dt)
select
a.dt+b.number as dt
from
(select cast('2010/12/31' as datetime) as dt
) as a
cross join master..spt_values as b
where
type = 'P' and number between 1 and 365
--译注：该方法效率很高，但需要master库的访问权限

-- 方法2.
;create table tc..dt
(id int identity(1,1)
,dt datetime)


;with tab as
(
select cast('2011-1-1' as datetime) as dt, 1 as rn
union all
select dateadd(day,1,dt) as dt,rn + 1 as rn
from tab
where rn < 365
)
insert into dt(dt)
select dt
from tab
option (maxrecursion 365)


-- 方法3.
;create table tc..dt
(id int identity(1,1)
,dt datetime)

insert into dt(dt)
;select
    cast('2010/12/31' as datetime)+number as dt
from master..spt_values as b
where
type = 'P' and number between 1 and 365
--译注：同方法1

-- 方法4.
;create table tc..dt
(id int identity(1,1)
,dt datetime)

insert into dt(dt)
select
dateadd(day, isnull((select scope_identity()),2),'2010/12/31')
go 365
--译注：该方法效率很低，不建议使用，如果仅作测试则可以使用

-- 方法5.
;create table tc..dt
(id int identity(1,1)
,dt datetime)

while  isnull(@@identity,1) <= 365
begin
    insert into dt(dt)
    select
        dateadd(day, isnull(@@identity,1),'2010/12/31') as dt
end