如何创建一张时间LIST表并插入到现有表中？
现有一张表A
  TIME  
2012-02-27 06:30
2012-02-28 05:30
……………………
2012-02-29 08:52 


现创造需要一张时间LIST表，这张表存放的是一天的每个时点:00:00、01:00、02:00……08:00…… 23:00

希望把时间LIST表中的数据插入到表A中，即表A的记录数乘以24，得到以下结果

  TIME HOUR
2012-02-27 06:30 00:00
2012-02-27 06:30 01:00
2012-02-27 06:30 02:00
…………
2012-02-27 06:30 23:00

2012-02-28 05:30 00:00
2012-02-28 05:30 01:00
2012-02-28 05:30 02:00
…………
2012-02-28 05:30 23:00


2012-02-29 08:52 00:00
2012-02-29 08:52 01:00
2012-02-29 08:52 02:00
…………
2012-02-29 08:52 23:00

这样得到新表以后方便计算耗时，请问如何实现？















   


 

------解决方案--------------------
没看懂你具体的意思,不过可参考做如下的改动.
以下是按照天的,你自己修改参数更改为小时的.
获取两个时间之内的所有日期

--sql 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2008-10-15 00:00:000'
set @edate = '2009-02-10 00:00:000'


select 
dateadd(dd,num,@sdate) dt
from 
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate


--sql 2005
declare @startDate datetime
declare @endDate datetime

SELECT @startDate = '2008-10-15' ,@endDate = '2009-01-23'
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate
)
SELECT tb.date from tb


生成日期的方法

方法一、

declare @t table(s datetime)
declare @s datetime,@s1 datetime
set @s = '2010-09-15'
set @s1 = '2010-10-15'
while @s <= @s1
begin 
insert into @t select @s
set @s = dateadd(dd,1,@s)
end
select * from @t


方法二、
select
dateadd(dd,number,'2010-09-15') as s
from
master..spt_values
where
type='p'
and
dateadd(dd,number,'2010-09-15')<='2010-10-15'


------解决方案--------------------

SQL code

create table t20
(times datetime)

insert into t20   
select '2012-02-27 06:30' union all
select '2012-02-28 05:30'


select c.times, 
case when len(d.number)=1 then ' 0'+d.number+':00' else d.number+':00' end 'hour'
from t20 c
inner join
(select a.d,b.number
from 
(select distinct convert(varchar,times,23) d from t20) a
cross join 
(select cast(number as varchar(2)) number
from master.dbo.spt_values where [type]='P' and number between 0 and 23) b
) d on convert(varchar,c.times,23)=d.d

times                   hour
----------------------- -------
2012-02-27 06:30:00       00:00
2012-02-27 06:30:00       01:00
2012-02-27 06:30:00       02:00
2012-02-27 06:30:00       03:00
2012-02-27 06:30:00       04:00
2012-02-27 06:30:00       05:00
2012-02-27 06:30:00       06:00
2012-02-27 06:30:00       07:00
2012-02-27 06:30:00       08:00
2012-02-27 06:30:00       09:00
2012-02-27 06:30:00      10:00
2012-02-27 06:30:00      11:00
2012-02-27 06:30:00      12:00
2012-02-27 06:30:00      13:00
2012-02-27 06:30:00      14:00
2012-02-27 06:30:00      15:00
2012-02-27 06:30:00      16:00
2012-02-27 06:30:00      17:00
2012-02-27 06:30:00      18:00
2012-02-27 06:30:00      19:00
2012-02-27 06:30:00      20:00
2012-02-27 06:30:00      21:00
2012-02-27 06:30:00      22:00
2012-02-27 06:30:00      23:00
2012-02-28 05:30:00       00:00
2012-02-28 05:30:00       01:00
2012-02-28 05:30:00       02:00
2012-