今天看到保哥一篇關於LINQ to SQL分頁問題的文章,裡面有一段範例是利用'User ' + cast(cast(rand() * 100 as int) as varchar(3))的技巧連續執行十次產生十筆測試用的資料。

這讓我想起之前在做CSV字串分割成陣列UDF意外學到的技巧,可以借用鮮為人知的master.dbo.spt_values系統資料表,讓SQL產生一段連號數字,這樣就能一口氣塞入連號的"User 1”, "User 2”, “User 3”… 測試資料。

查詢SELECT DISTINCT NUMBER FROM master.dbo.spt_values WHERE name IS NULL可傳回一段0到2047的數字,稍稍加工就可得到我們想要的結果:

INSERT INTO Table_1 (Name)
SELECT 'User ' + CONVERT(VARCHAR(4), N)
FROM 
(
    SELECT DISTINCT NUMBER AS N
    FROM master.dbo.spt_values
    WHERE name IS NULL
) NumberPool
WHERE N BETWEEN 1 AND 100
ORDER BY N

是不是很方便呢? 不過如果只有2048個不夠用怎麼辦?

SELECT X.NUMBER * 2048 + Y.NUMBER AS N
FROM
(
    SELECT DISTINCT NUMBER 
    FROM master.dbo.spt_values 
    WHERE name IS NULL
) AS X 
JOIN
(
    SELECT DISTINCT NUMBER 
    FROM master.dbo.spt_values 
    WHERE name IS NULL
) AS Y
ON 1 = 1

2048*2048,一口氣飆到四百萬(0到4,194,303,執行約需40秒),夠用了吧?


Comments

# by 默默

純閒聊,如果只是要數到 100 的話,用 CTE 的遞迴來做比較快: with CTEtemp (tmpId, tmpRow) as (select 1, 1), CTEall(tmpId, tmpRow) as (select tmpId, tmpRow from CTEtemp union all select CTEall.tmpId, CTEall.tmpRow + 1 from CTEall join CTEtemp on CTEall.tmpId = CTEtemp.tmpId where CTEall.tmpRow < 100 ) select tmpRow from CTEall; ps. 要超過 100 可以用黑暗大第二個方法,把 CTEall join 兩次即可 (不過數量沒辦法產生那麼多啦)。

# by asin

想到以前老師教的... declare @total as int declare @user as table ( id int not null primary key ) declare @cnt as int set @total = 100000 set @cnt = 1 insert @user values (1) while @cnt * 2 <= @total begin insert @user select id+@cnt from @user set @cnt = @cnt * 2 end insert into @user select id + @cnt from @user where id + @cnt <= @total select id from @user

# by

DECLARE @數量 INT = 100000 ;WITH tmp(tmpId) AS ( SELECT 1 tmpId UNION ALL SELECT tmpId+1 FROM tmp WHERE tmpId < @數量 ) SELECT * FROM tmp OPTION (MAXRECURSION 0) 遞迴加上 OPTION (MAXRECURSION 0) 就可突破100個上限 不過請小心服用

Post a comment