SQL Serverのパーティション分割の検証で、大量に日付をを含むテストデータが欲しかったので、
http://d.hatena.ne.jp/dotnetmemo/20111015/1318663995
http://d.hatena.ne.jp/qaz76/20110512/1305251400
を参考にしてみた。
先輩方に感謝！
[code]
# テーブル作成
CREATE TABLE [dbo].[TestTable](
[id] [int] NULL,
[data] [varchar](50) NULL,
[add_date] [datetime2](7) NULL
)
GO
[/code]
[code]
— ランダムな日付返す
CREATE VIEW
V_RAND
AS
SELECT RAND() RND
GO
–UDF
CREATE FUNCTION
RANDNUM
(
@START AS INT,
@END AS INT
)
RETURNS INT
AS
BEGIN
RETURN (SELECT FLOOR(@START + (RND * (@END – @START + 1))) FROM V_RAND)
END
GO
–UDF
CREATE FUNCTION
RANDDATE
(
@START AS DATETIME,
@END AS DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(DAY, DBO.RANDNUM(0, DATEDIFF(DAY, @START, @END)), @START)
END
GO
[/code]
[code]
— 100万件レコード作る
— add_dateには、2015/01/01から2016/07/31までの日付をランダムでくっつける（重複あり）
Declare @p_NumberOfRows Bigint
Select @p_NumberOfRows=1000000;
With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n From Expand
)
INSERT INTO TestTable
Select n, ‘DATA’ + right(‘0000000000’ + convert(varchar, n), 10),dbo.RANDDATE(‘2015/01/01’, ‘2016/07/31’)
from Nums Where n<=@p_NumberOfRows
OPTION (MaxRecursion 0);
[/code]