一次性插入一年时间
能不能请教各位高手一个问题，怎样在SQL Server中一次性插入一年的时间呢？有没有这样的系统存储过程或者函数？
比如说一次性插入
列名：年份 月份 日次
2012 1 1
2012 1 2
2012 1 3
2012 1 4
2012 1 5
2012 1 6
2012 1 7
2012 1 8
2012 1 9
……
等所有2012年的时间
[解决办法]
declare @date1 datedeclare @date2 dateset @date1='2012-01-01'set @date2='2012-12-31';with tas(select DATEADD(DD,number,@date1) as [date]from master..spt_valueswhere number between 0 and DATEDIFF(DD,@date1,@date2) and type='p')select YEAR([date]) as [year], MONTH([date]) as [month], DAY([date]) as [day] into #tblfrom tselect * from #tbl/*year month day2012 1 12012 1 22012 1 32012 1 42012 1 52012 1 62012 1 72012 1 82012 1 92012 1 102012 1 112012 1 122012 1 132012 1 142012 1 152012 1 162012 1 172012 1 182012 1 192012 1 202012 1 212012 1 222012 1 232012 1 242012 1 252012 1 262012 1 272012 1 282012 1 292012 1 302012 1 312012 2 12012 2 22012 2 32012 2 42012 2 52012 2 62012 2 72012 2 82012 2 92012 2 102012 2 112012 2 122012 2 132012 2 142012 2 152012 2 162012 2 172012 2 182012 2 192012 2 202012 2 212012 2 222012 2 232012 2 242012 2 252012 2 262012 2 272012 2 282012 2 292012 3 12012 3 22012 3 32012 3 42012 3 52012 3 62012 3 72012 3 82012 3 92012 3 102012 3 112012 3 122012 3 132012 3 142012 3 152012 3 162012 3 172012 3 182012 3 192012 3 202012 3 212012 3 222012 3 232012 3 242012 3 252012 3 262012 3 272012 3 282012 3 292012 3 302012 3 312012 4 12012 4 22012 4 32012 4 42012 4 52012 4 62012 4 72012 4 82012 4 92012 4 102012 4 112012 4 122012 4 132012 4 142012 4 152012 4 162012 4 172012 4 182012 4 192012 4 202012 4 212012 4 222012 4 232012 4 242012 4 252012 4 262012 4 272012 4 282012 4 292012 4 302012 5 12012 5 22012 5 32012 5 42012 5 52012 5 62012 5 72012 5 82012 5 92012 5 102012 5 112012 5 122012 5 132012 5 142012 5 152012 5 162012 5 172012 5 182012 5 192012 5 202012 5 212012 5 222012 5 232012 5 242012 5 252012 5 262012 5 272012 5 282012 5 292012 5 302012 5 312012 6 12012 6 22012 6 32012 6 42012 6 52012 6 62012 6 72012 6 82012 6 92012 6 102012 6 112012 6 122012 6 132012 6 142012 6 152012 6 162012 6 172012 6 182012 6 192012 6 202012 6 212012 6 222012 6 232012 6 242012 6 252012 6 262012 6 272012 6 282012 6 292012 6 302012 7 12012 7 22012 7 32012 7 42012 7 52012 7 62012 7 72012 7 82012 7 92012 7 102012 7 112012 7 122012 7 132012 7 142012 7 152012 7 162012 7 172012 7 182012 7 192012 7 202012 7 212012 7 222012 7 232012 7 242012 7 252012 7 262012 7 272012 7 282012 7 292012 7 302012 7 312012 8 12012 8 22012 8 32012 8 42012 8 52012 8 62012 8 72012 8 82012 8 92012 8 102012 8 112012 8 122012 8 132012 8 142012 8 152012 8 162012 8 172012 8 182012 8 192012 8 202012 8 212012 8 222012 8 232012 8 242012 8 252012 8 262012 8 272012 8 282012 8 292012 8 302012 8 312012 9 12012 9 22012 9 32012 9 42012 9 52012 9 62012 9 72012 9 82012 9 92012 9 102012 9 112012 9 122012 9 132012 9 142012 9 152012 9 162012 9 172012 9 182012 9 192012 9 202012 9 212012 9 222012 9 232012 9 242012 9 252012 9 262012 9 272012 9 282012 9 292012 9 302012 10 12012 10 22012 10 32012 10 42012 10 52012 10 62012 10 72012 10 82012 10 92012 10 102012 10 112012 10 122012 10 132012 10 142012 10 152012 10 162012 10 172012 10 182012 10 192012 10 202012 10 212012 10 222012 10 232012 10 242012 10 252012 10 262012 10 272012 10 282012 10 292012 10 302012 10 312012 11 12012 11 22012 11 32012 11 42012 11 52012 11 62012 11 72012 11 82012 11 92012 11 102012 11 112012 11 122012 11 132012 11 142012 11 152012 11 162012 11 172012 11 182012 11 192012 11 202012 11 212012 11 222012 11 232012 11 242012 11 252012 11 262012 11 272012 11 282012 11 292012 11 302012 12 12012 12 22012 12 32012 12 42012 12 52012 12 62012 12 72012 12 82012 12 92012 12 102012 12 112012 12 122012 12 132012 12 142012 12 152012 12 162012 12 172012 12 182012 12 192012 12 202012 12 212012 12 222012 12 232012 12 242012 12 252012 12 262012 12 272012 12 282012 12 292012 12 302012 12 31*/
[解决办法]
create table tb (year int,month int, day int)goset datefirst 1declare @date_day datetimeset @date_day ='2012-01-05' ----手动输入（替换）每个月的月数即可declare @month_day intset @month_day=datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@date_day) as varchar)+'-'+cast(month(@date_day) as varchar)+'-01' as datetime))))declare @month_first_day datetimeset @month_first_day= convert(varchar(10),dateadd(dd,-DatePart(day,dateadd(dd,-1,@date_day)),@date_day),120)insert into tbselect year(dateadd(day,number,@month_first_day)),MONTH(dateadd(day,number,@month_first_day)),DAY(dateadd(day,number,@month_first_day)) from master.dbo.spt_values where type='p' and number <@month_day go select * from tb /* year month day2012 1 12012 1 22012 1 32012 1 42012 1 52012 1 62012 1 72012 1 82012 1 92012 1 102012 1 112012 1 122012 1 132012 1 142012 1 152012 1 162012 1 172012 1 182012 1 192012 1 202012 1 212012 1 222012 1 232012 1 242012 1 252012 1 262012 1 272012 1 282012 1 292012 1 302012 1 31*/
[解决办法]
declare @date1 datedeclare @date2 dateset @date1='2012-01-01'set @date2='2012-12-31';with tas(select px=ROW_NUMBER()over(order by number), DATEADD(DD,number,@date1) as [date]from master..spt_valueswhere number between 0 and DATEDIFF(DD,@date1,@date2) and type='p'),m as(select px, YEAR([date]) as [year], MONTH([date]) as [month], DAY([date]) as [day] into #tblfrom t),nas(select px=ROW_NUMBER()over(order by ID), IDfrom 另外一张表)insert tbselect n.id, m.[year], m.[month], m.[day]from minner join non m.px=n.px
