我想在表中添加可变数量的记录(天)
我看到了一个整洁的解决方案：
SET @nRecords=DATEDIFF(d,'2009-01-01',getdate()) SET ROWCOUNT @nRecords INSERT int(identity,1) INTO #temp FROM sysobjects a,sysobjects b SET ROWCOUNT 0
但可悲的是,在UDF中不起作用(因为#temp和SET ROWCOUNT).有什么想法能如何实现？
解决方法
如果您使用sql 2005或更高版本,则可以使用递归CTE获取日期或数字列表…
with MyCte AS
(select MyCounter = 0
UNION ALL
SELECT MyCounter + 1
FROM MyCte
where MyCounter < DATEDIFF(d,getdate()))
select MyCounter,DATEADD(d,MyCounter,'2009-01-01')
from MyCte
option (maxrecursion 0)
/* output...
MyCounter MyDate
----------- -----------------------
0 2009-01-01 00:00:00.000
1 2009-01-02 00:00:00.000
2 2009-01-03 00:00:00.000
3 2009-01-04 00:00:00.000
4 2009-01-05 00:00:00.000
5 2009-01-06 00:00:00.000
....
170 2009-06-20 00:00:00.000
171 2009-06-21 00:00:00.000
172 2009-06-22 00:00:00.000
173 2009-06-23 00:00:00.000
174 2009-06-24 00:00:00.000
(175 row(s) affected)
*/
