generate fixed number of rows in a table

Question

Not able to word the question properly, so couldn't search what I want. All I need is a dummy table with a single column of say guids, which I use it for some other purposes. Without actually writing same insert .. newID() n times, wondering if there is an elegant solution.

Similar question would be how do I populate a blank table with a int column with say 1-n numbers.

Row1: 1
Row2: 2
.......
Row100:100

Solution 1

This method is blisteringly fast. If you need to generate a numbers table from nothing, it's probably the "best" means available.

WITH
  t0(i) AS (SELECT 0 UNION ALL SELECT 0),
  t1(i) AS (SELECT 0 FROM t0 a, t0 b),
  t2(i) AS (SELECT 0 FROM t1 a, t1 b),
  t3(i) AS (SELECT 0 FROM t2 a, t2 b),
  n(i) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM t3)
SELECT i FROM n WHERE i BETWEEN 1 AND 100

Solution 2

Instead of a recursive CTE, I recommend a set-based approach from any object you know already has more than 100 rows.

--INSERT dbo.newtable(ID, GUID)
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY [object_id]), NEWID()
  FROM sys.all_columns ORDER BY [object_id];

For plenty of other ideas, see this series:

• http://www.sqlperformance.com/generate-a-set-1
• http://www.sqlperformance.com/generate-a-set-2
• http://www.sqlperformance.com/generate-a-set-3

Solution 3

One way;

;with guids( i, guid ) as
(
    select 1 as i, newid() 
        union all
    select i + 1, newid() 
        from guids
        where i < 100
)

select guid from guids option (maxrecursion 100)