如何向一个表的列循环插入一年的日期

Question

比如表 worklogs有一列 riqi现要求循环插入本年度（2011）的全部日期  riqi         ...
2011-01-01     ...
2011-01-02     ...

Answer 1

SELECT TOP 1000 Num=IDENTITY(INT,0,1) INTO Num FROM syscolumns
DECLARE @d1 DATETIME, @d2 DATETIME      
SELECT @d1 = '2011-01-01', @d2 = '2011-12-31'      
SELECT [day] = CONVERT(VARCHAR, DATEADD(dd, Num, @d1), 23)
FROM Num
WHERE Num <= DATEDIFF(dd, @d1, @d2)   --如不再需要Num表
DROP TABLE Num 这个可以获得一年的每一天

Answer 2

DECLARE @dt1 DATETIME
SET @dt1='2011-01-01'SELECT @dt1+number AS dt
FROM master.dbo.spt_values AS a
WHERE type='P' AND @dt1+number<'2012-01-01'

Answer 3

DECLARE @dt1 DATETIME
SET @dt1='2011-01-01'insert into 表(日期列)
SELECT @dt1+number AS dt
FROM master.dbo.spt_values AS a
WHERE type='P' AND @dt1+number<'2012-01-0

Answer 4

insert into worklogs(riqi)
select dateadd(d,number,'2011-01-01')
from master..spt_values
where type='p' and year(dateadd(d,number,'2011-01-01'))=2011

Answer 5

DECLARE @dt1 DATETIME
SET @dt1='2011-01-01'insert into YL_WorkLogs(riqi) 
SELECT @dt1+number AS dt
FROM master.dbo.spt_values AS a
WHERE type='P' AND @dt1+number<'2012-01-0有错误啊

Answer 6

CREATE TABLE YL_WorkLogs(riqi DATETIME)
go
DECLARE @dt1 DATETIME
SET @dt1='2011-01-01'insert into YL_WorkLogs(riqi) 
SELECT @dt1+number AS dt
FROM master.dbo.spt_values AS a
WHERE type='P' AND @dt1+number<'2012-01-01'
GO
SELECT * FROM YL_WorkLogs

Answer 7

谢谢两位，可现在表中有一列不能为空，我想在其中插入一个值，insert into worklogs(dwmc,riqi) .....
select dateadd(d,number,'2011-01-01')
from master..spt_values
where type='p' and year(dateadd(d,number,'2011-01-01'))=2011比如dwmc一列插入'bj' 该怎么修改呢？

Answer 8

if object_id('worklogs','U') is not null
   drop table worklogs
go
create table worklogs
(
 riqi varchar(10)
)
go
insert into worklogs
select convert(varchar(10),riqi,120) from(select riqi=dateadd(dd,number,'2010-12-31') from master..spt_values where type='p' and number between 1 and 366) a where datepart(yy,a.riqi)=2011
select * from worklogs
/*
riqi
----------
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
2011-01-06
2011-01-07
2011-01-08
2011-01-09
2011-01-10
2011-01-11
2011-01-12
2011-01-13
2011-01-14
2011-01-15
2011-01-16
2011-01-17
2011-01-18
2011-01-19
2011-01-20
2011-01-21
2011-01-22
2011-01-23
2011-01-24
2011-01-25
2011-01-26
2011-01-27
2011-01-28
2011-01-29
2011-01-30
2011-01-31
2011-02-01
2011-02-02
2011-02-03
2011-02-04
2011-02-05
2011-02-06
2011-02-07
2011-02-08
2011-02-09
2011-02-10
2011-02-11
2011-02-12
2011-02-13
2011-02-14
2011-02-15
2011-02-16
2011-02-17
2011-02-18
2011-02-19
2011-02-20
2011-02-21
2011-02-22
2011-02-23
2011-02-24
2011-02-25
2011-02-26
2011-02-27
2011-02-28
2011-03-01
2011-03-02
2011-03-03
2011-03-04
2011-03-05
2011-03-06
2011-03-07
2011-03-08
2011-03-09
2011-03-10
2011-03-11
2011-03-12
2011-03-13
2011-03-14
2011-03-15
2011-03-16
2011-03-17
2011-03-18
2011-03-19
2011-03-20
2011-03-21
2011-03-22
2011-03-23
2011-03-24
2011-03-25
2011-03-26
2011-03-27
2011-03-28
2011-03-29
2011-03-30
2011-03-31
2011-04-01
2011-04-02
2011-04-03
2011-04-04
2011-04-05
2011-04-06
2011-04-07
2011-04-08
2011-04-09
2011-04-10
2011-04-11
2011-04-12
2011-04-13
2011-04-14
2011-04-15
2011-04-16
2011-04-17
2011-04-18
2011-04-19
2011-04-20
2011-04-21
2011-04-22
2011-04-23
2011-04-24
2011-04-25
2011-04-26
2011-04-27
2011-04-28
2011-04-29
2011-04-30
2011-05-01
2011-05-02
2011-05-03
2011-05-04
2011-05-05
2011-05-06
2011-05-07
2011-05-08
2011-05-09
2011-05-10
2011-05-11
2011-05-12
2011-05-13
2011-05-14
2011-05-15
2011-05-16
2011-05-17
2011-05-18
2011-05-19
2011-05-20
2011-05-21
2011-05-22
2011-05-23
2011-05-24
2011-05-25
2011-05-26
2011-05-27
2011-05-28
2011-05-29
2011-05-30
2011-05-31
2011-06-01
2011-06-02
2011-06-03
2011-06-04
2011-06-05
2011-06-06
2011-06-07
2011-06-08
2011-06-09
2011-06-10
2011-06-11
2011-06-12
2011-06-13
2011-06-14
2011-06-15
2011-06-16
2011-06-17
2011-06-18
2011-06-19
2011-06-20
2011-06-21
2011-06-22
2011-06-23
2011-06-24
2011-06-25
2011-06-26
2011-06-27
2011-06-28
2011-06-29
2011-06-30
2011-07-01
2011-07-02
2011-07-03
2011-07-04
2011-07-05
2011-07-06
2011-07-07
2011-07-08
2011-07-09
2011-07-10
2011-07-11
2011-07-12
2011-07-13
2011-07-14
2011-07-15
2011-07-16
2011-07-17
2011-07-18
2011-07-19
2011-07-20
2011-07-21
2011-07-22
2011-07-23
2011-07-24
2011-07-25
2011-07-26
2011-07-27
2011-07-28
2011-07-29
2011-07-30
2011-07-31
2011-08-01
2011-08-02
2011-08-03
2011-08-04
2011-08-05
2011-08-06
2011-08-07
2011-08-08
2011-08-09
2011-08-10
2011-08-11
2011-08-12
2011-08-13
2011-08-14
2011-08-15
2011-08-16
2011-08-17
2011-08-18
2011-08-19
2011-08-20
2011-08-21
2011-08-22
2011-08-23
2011-08-24
2011-08-25
2011-08-26
2011-08-27
2011-08-28
2011-08-29
2011-08-30
2011-08-31
2011-09-01
2011-09-02
2011-09-03
2011-09-04
2011-09-05
2011-09-06
2011-09-07
2011-09-08
2011-09-09
2011-09-10
2011-09-11
2011-09-12
2011-09-13
2011-09-14
2011-09-15
2011-09-16
2011-09-17
2011-09-18
2011-09-19
2011-09-20
2011-09-21
2011-09-22
2011-09-23
2011-09-24
2011-09-25
2011-09-26
2011-09-27
2011-09-28
2011-09-29
2011-09-30
2011-10-01
2011-10-02
2011-10-03
2011-10-04
2011-10-05
2011-10-06
2011-10-07
2011-10-08
2011-10-09
2011-10-10
2011-10-11
2011-10-12
2011-10-13
2011-10-14
2011-10-15
2011-10-16
2011-10-17
2011-10-18
2011-10-19
2011-10-20
2011-10-21
2011-10-22
2011-10-23
2011-10-24
2011-10-25
2011-10-26
2011-10-27
2011-10-28
2011-10-29
2011-10-30
2011-10-31
2011-11-01
2011-11-02
2011-11-03
2011-11-04
2011-11-05
2011-11-06
2011-11-07
2011-11-08
2011-11-09
2011-11-10
2011-11-11
2011-11-12
2011-11-13
2011-11-14
2011-11-15
2011-11-16
2011-11-17
2011-11-18
2011-11-19
2011-11-20
2011-11-21
2011-11-22
2011-11-23
2011-11-24
2011-11-25
2011-11-26
2011-11-27
2011-11-28
2011-11-29
2011-11-30
2011-12-01
2011-12-02
2011-12-03
2011-12-04
2011-12-05
2011-12-06
2011-12-07
2011-12-08
2011-12-09
2011-12-10
2011-12-11
2011-12-12
2011-12-13
2011-12-14
2011-12-15
2011-12-16
2011-12-17
2011-12-18
2011-12-19
2011-12-20
2011-12-21
2011-12-22
2011-12-23
2011-12-24
2011-12-25
2011-12-26
2011-12-27
2011-12-28
2011-12-29
2011-12-30
2011-12-31(365 行受影响)*/

Answer 9

insert into YL_WorkLogs(dwmcpy,riqi)
values('beijing',select dateadd(d,number,'2011-01-01')
from master..spt_values
where type='p' and year(dateadd(d,number,'2011-01-01'))=2011
)这么写怎么有错误呢？

Answer 10

DECLARE @dt1 DATETIME
SET @dt1='2011-01-01'insert into worklogs(dwmc,riqi)
SELECT 'beijing' AS dwmc,@dt1+number AS riqi
FROM master.dbo.spt_values AS a
WHERE type='P' AND @dt1+number<'2012-01-01'這樣用