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

Take a look at this previous question: http://stackoverflow.com/questions/1041163/inserting-n-number-of-records-with-t-sql — sr28, Jan 21 '14 at 17:04
see this question: http://stackoverflow.com/q/1393951/65223 about numbers tables — KM., Jan 21 '14 at 17:05

score 7 · Answer 1 · answered Jan 21 '14 at 17:07

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:

score 6 · Answer 2 · answered Jan 21 '14 at 17:03

6

You can do it recursively.

For numbers, f.ex.:

WITH r AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 FROM r WHERE n+1<=100
)
SELECT * FROM r

answered Jan 21 '14 at 17:03

2

Won't work for 101 rows and more - default CTE nesting limit – too Nov 01 '18 at 15:17

score 2 · Answer 3 · answered Jan 21 '14 at 17:05

2

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)

answered Jan 21 '14 at 17:05

Alex K.

165,803
30
257
277

score 2 · Accepted Answer · answered Jan 21 '14 at 17:15

2

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

answered Jan 21 '14 at 17:15

Anon

10,214
1
27
31

1

I'm not sure I'd call 2.7 milliseconds per execution "blisteringly fast" compared to 3.1 milliseconds for other, simpler variations on this page, but ok (don't forget to generate the GUID when testing too). :-) – Aaron Bertrand Jan 22 '14 at 15:03

score 0 · Answer 5 · answered Oct 26 '20 at 20:55

Just adding this as it wasn't listed:

A quick way to get 10 rows:

SELECT ROW_NUMBER() OVER(
        ORDER BY N1.N) 
        , LOWER(NEWID())
    FROM   (VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N1(N)          -- 10

If you want it to be based on a variable:

DECLARE  @N int = 10;
WITH Numbers(number)
     AS (SELECT ROW_NUMBER() OVER(
                ORDER BY N1.N)
         FROM   (VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N1(N)          -- 10
                CROSS JOIN(VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) AS N2(N)-- 100  
                --CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N3 (N)   -- 1,000
                --CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N4 (N)   -- 10,000
                --CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N5 (N) -- 100,000
                -- Etc....
)
     SELECT *
          , LOWER(NEWID())
     FROM   Numbers
     WHERE  number <= @N;

generate fixed number of rows in a table

5 Answers5

Linked