INSERT A(月分) SELECT '2008-01' UNION SELECT '2008-02'.......
就是2楼的意思。不过只有这个办法?如果从2000年开始,我要UNION select 到什么时候?
declare @date datetime,@end datetime,@i int set @i=0 set @date='2008-01-01' set @end='2009-06-01' while(datediff(m,@date,@end)>=0) begin insert into A(id,月份) select @i,@date set @i=@i+1 set @date=dateadd(m,1,@date) end
create table a(id int, 月份 varchar(7)) go declare @date datetime,@end datetime,@i int set @i=0 set @date='2008-01-01' set @end='2009-06-01' while(datediff(m,@date,@end)>=0) begin insert into A(id,月份) select @i,convert(char(7),@date,120) set @i=@i+1 set @date=dateadd(m,1,@date) end go select * from a; go drop table a go
declare @start datetime,@end datetime set @start='2008-01' set @end='2009-06' while @start<=@end begin as insert into select convert(varchar(7),@start,120) set @start=dateadd(mm,1,@start) end
if object_id('a') is not null drop table a create table a(id int identity(1,1) ,月份 varchar(7) ) declare @start datetime,@end datetime set @start='2008-01-01' set @end='2009-06-01' while (@start<=@end) begin insert into a values( convert(varchar(7),@start,120)) set @start=dateadd(mm,1,@start) endselect * from a /*------------------- 1 2008-01 2 2008-02 3 2008-03 4 2008-04 5 2008-05 6 2008-06 7 2008-07 8 2008-08 9 2008-09 10 2008-10 11 2008-11 12 2008-12 13 2009-01 14 2009-02 15 2009-03 16 2009-04 17 2009-05 18 2009-06----------------------*/
set @i=0
set @date='2008-01-01'
set @end='2009-06-01'
while(datediff(m,@date,@end)>=0)
begin
insert into A(id,月份) select @i,@date
set @i=@i+1
set @date=dateadd(m,1,@date)
end
go
declare @date datetime,@end datetime,@i int
set @i=0
set @date='2008-01-01'
set @end='2009-06-01'
while(datediff(m,@date,@end)>=0)
begin
insert into A(id,月份) select @i,convert(char(7),@date,120)
set @i=@i+1
set @date=dateadd(m,1,@date)
end
go
select * from a;
go
drop table a
go
set @start='2008-01'
set @end='2009-06'
while @start<=@end
begin as
insert into select convert(varchar(7),@start,120)
set @start=dateadd(mm,1,@start)
end
(
[ID] int identity(1,1),
[月份] varchar(7)
)
GODECLARE
@smonth varchar(7),
@emonth varchar(7)
SELECT
@smonth='2008-01',
@emonth='2009-06'INSERT A(月份)
SELECT
CONVERT(VARCHAR(7),DATEADD(month,number,@smonth+'-01'),120)
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(month,number,@smonth+'-01')<=@emonth+'-01'
SELECT * FROM ADROP TABLE A/**
ID 月份
----------- -------
1 2008-01
2 2008-02
3 2008-03
4 2008-04
5 2008-05
6 2008-06
7 2008-07
8 2008-08
9 2008-09
10 2008-10
11 2008-11
12 2008-12
13 2009-01
14 2009-02
15 2009-03
16 2009-04
17 2009-05
18 2009-06(所影响的行数为 18 行)**/
select @i=1,@begin='2008-01-01',@end='2009-06-01'while dateadd(mm,@i,@begin)<=@end
begin
insert into @tb
values(@i,year(dateadd(mm,@i,@begin)),month(dateadd(mm,@i,@begin)))
select @i=@i+1
endSELECT * from @tb
id y mon
----------- ----------- -----------
1 2008 2
2 2008 3
3 2008 4
4 2008 5
5 2008 6
6 2008 7
7 2008 8
8 2008 9
9 2008 10
10 2008 11
11 2008 12
12 2009 1
13 2009 2
14 2009 3
15 2009 4
16 2009 5
17 2009 6
(17 行受影响)
is not null drop table a
create table a(id int identity(1,1) ,月份 varchar(7) )
declare @start datetime,@end datetime
set @start='2008-01-01'
set @end='2009-06-01'
while (@start<=@end)
begin
insert into a
values( convert(varchar(7),@start,120))
set @start=dateadd(mm,1,@start)
endselect * from a
/*-------------------
1 2008-01
2 2008-02
3 2008-03
4 2008-04
5 2008-05
6 2008-06
7 2008-07
8 2008-08
9 2008-09
10 2008-10
11 2008-11
12 2008-12
13 2009-01
14 2009-02
15 2009-03
16 2009-04
17 2009-05
18 2009-06----------------------*/