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

Aaron Bertrand · Answer 1 · 2023-08-09T12:29:53.460

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:

In newer versions (SQL Server 2022 and modern versions of Azure SQL Database / Managed Instance), you can use the new GENERATE_SERIES() function:

SELECT value FROM GENERATE_SERIES(1, 100);

Or on 2016+, you can trick STRING_SPLIT() into generating 100 rows from a string of 99 commas (or any character, really):

SELECT value = rn FROM 
(
  SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
  FROM STRING_SPLIT(REPLICATE(',', 99), ',')
) AS x;

And if you need more than 8,000 values:

SELECT value = rn FROM 
(
  SELECT rn = ROW_NUMBER() OVER (ORDER BY @@SPID)
  FROM STRING_SPLIT(REPLICATE(CONVERT(varchar(max),','),79999), ',')
) AS x;

These all get around the need to have an existing table with enough rows to match your sequence needs or to be able to access system/catalog views.

FWIW, in a brand new empty database in SQL Server 2022 the `sys.all_columns` view contains **12,216** rows - so if you want to generate more rows you can do a `CROSS JOIN` on itself to generate 149,230,656 rows. — Dai, Sep 09 '22 at 22:42

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 6 · Accepted Answer · edited Sep 09 '22 at 22:52

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), --             2 rows
t1(i) AS (SELECT 0 FROM t0 a, t0 b),    --             4 rows
t2(i) AS (SELECT 0 FROM t1 a, t1 b),    --            16 rows
t3(i) AS (SELECT 0 FROM t2 a, t2 b),    --           256 rows
--t4(i) AS (SELECT 0 FROM t3 a, t3 b),  --        65,536 rows
--t5(i) AS (SELECT 0 FROM t4 a, t4 b),  -- 4,294,967,296 rows

n(i) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM t3)
SELECT i FROM n WHERE i BETWEEN 1 AND 100

Regarding performance:

Using SQL Server 2022, on a Xeon box from 2016, with SET STATISTICS TIME ON to measure query time I got these numbers:
- (With t4 and t5 commented-out), it generates 256 rows in "0ms".
- (With t4 uncommented) it generates 65,536 rows in 53ms.
- (With t5 uncommented in an INSERT FROM) it generated and inserted 4bn rows to a TABLE on-disk in about 65 minutes.
  - That's 66 million rows per minute, or about a million rows per second, nice!

Explanation:

The first CTE, t0 generates 2 rows.
Each subsequent CTE performs a CROSS JOIN of the previous CTE; a CROSS JOIN is a Cartesian Product which effectively squares the number of rows in each CTE step.
- So having t0 through t3 means performing the Cartesian product three times, thus generating rows.
- SELECT 0 FROM t0 a, t0 b is the same thing as SELECT 0 FROM t0 AS a CROSS JOIN t0 AS b.

Note that the results start at 1 and not 0 because ROW_NUMBER() starts at 1. To start at 0 do SELECT ( i - 1 ) FROM n in the outermost query.

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 3 · Answer 4 · answered Jan 21 '14 at 17:05

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)

answered Jan 21 '14 at 17:05

Alex K.

171,639
30
264
288

maxrecursion 0 also works as no limit (max being 32,767) – Mladen Mihajlovic Jul 18 '23 at 09:58

score 2 · 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