怎样得到两个datetime类型之间的每一天的列表？

[bjzhanghao]

有一个表r(id,dt,count)，dt是datetime类型的，现在表里的记录的dt字段值从2003-4-1到2003-6-1，但不是每天都有记录。想利用一个查询得到每天的记录，没有记录的条目的count字段填0。应该怎么写这样的查询呢？
我感觉好象应该从最小的日期到最大的日期循环加上去，但具体要怎么写呢？

[CrazyFor]

1，建序数表
select top 8000 identity(int,0,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c

2,
select cast('2003-4-1' as datetime)+a.n
from numtab a left join r b on convert(char(10),cast('2003-4-1' as datetime)+a.n,120)=convert(char(10),b.dt,120)
where cast('2003-4-1' as datetime)+a.n<='2003-06-01'

[CrazyFor]

更正:添加count字段.

1，建序数表
select top 8000 identity(int,0,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c

2,
select cast('2003-4-1' as datetime)+a.n ,isnull(b.count,0)
from numtab a left join r b on convert(char(10),cast('2003-4-1' as datetime)+a.n,120)=convert(char(10),b.dt,120)
where cast('2003-4-1' as datetime)+a.n<='2003-06-01'

[愉快的登山者]

declare @t datetime
declare @m datetime
select * into #t from r where 1 = 2
select @t = min(dt) from r
select @m = max(dt) from r
while @t <= @m
begin
if exists (select * from r where dt = @t)
insert #t select id, dt, count from r where dt = @t
else
insert #t select 0, @t, 0
@t = dateadd(day, 1, @t)
end
select * from #t order by dt
go

[bjzhanghao]

@t = dateadd(day, 1, @t)这一行有错误，是不是要加Set？
#t用不用drop掉，是drop #t还是drop t？

[bjzhanghao]

总提示“插入错误: 列名或所提供值的数目与表定义不匹配。”怎么办？
在insert #t select id, dt, count from r where dt = @t
和insert #t select 0, @t, 0
这两句上