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

歡迎推文分享:
Published 04 June 2009 05:45 AM 由 Jeffrey
Filed under: , ,
Views: 17,327



意見

# 默默 said on 10 June, 2009 03:10 AM

純閒聊,如果只是要數到 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 兩次即可 (不過數量沒辦法產生那麼多啦)。

# asin said on 11 July, 2009 03:47 AM

想到以前老師教的...

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

#said on 09 August, 2012 05:13 AM

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個上限

不過請小心服用

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<June 2009>
SunMonTueWedThuFriSat
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

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

文章典藏
其他功能

這個部落格


Syndication