I recently profiled on my site at http://www.helpwithsql.com/2011/05/cartesian-join-for-numbers/ a query that can be used for using cross joins to populate a numbers table. I'm reposting the sql for doing this here. This runs in ~3 seconds and currently inserts 800,000 records. This can be modified to insert fewer or more numbers as needed.
A few examples of number tables in use can be found here [scroll halfway down]: http://www.sqlservercentral.com/articles/Advanced%20Querying/2547/
Here's the SQL. I used a table variable @numbers for the numbers 0-9, but if you're using SQL Server 2000, you'll need to instead use a temp table [#numbers].
create table numbers(number int)
----- Use a table variable
DECLARE @numbers TABLE
(
num INT
)
----- Populate a table with numbers 0 to 9
DECLARE @counter INT
SET @counter = 0
While @counter < 10
BEGIN
INSERT INTO @numbers VALUES(@counter)
SET @counter = @counter + 1
END
INSERT INTO numbers (number)
SELECT
(
n100000place.num*100000 +
n10000place.num*10000 +
n1000place.num*1000 +
n100place.num*100 +
n10place.num*10 +
n1place.num
) AS 'number'
FROM @numbers n1place
CROSS JOIN @numbers n10place
CROSS JOIN @numbers n100place
CROSS JOIN @numbers n1000place
CROSS JOIN @numbers n10000place
CROSS JOIN @numbers n100000place
WHERE n100000place.num < 8 ----- < 800000
ORDER BY number
Visit http://www.helpwithsql.com
Numbers table from cross join
Posted - 05/29/2011 : 00:36:00
