Thursday, June 04, 2009 - 文章

SQL Server, 請幫我從1數到100

今天看到保哥一篇關於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秒)，夠用了吧?

Posted 04 June 2009 05:45 AM 由 Jeffrey | 2 comment(s)
Filed under: , ,

搜尋

Go

<June 2009>
SunMonTueWedThuFriSat
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011
 
RSS
【工商服務】
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人，十年的IT職場生涯，寫過系統、管過專案， 也帶過團隊，最後還是無怨無悔地選擇了技術鑽研這條路，近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


BlogLook Score and Rank

Syndication