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秒)，夠用了吧?