Generate result set of a certain length

Question

I need to insert certain amount of rows into some table with values taken from variables. I certainly can do a loop inserting single row at a time, but that's too straightforward. I am looking for more elegant solution. My current thoughts are around INSERT INTO ... SELECT ... statement, but now I need a query that will generate the amount of rows that I need. I tried to write recursive CTE to do it:

CREATE FUNCTION ufGenerateRows(@numRows INT = 1)
RETURNS @RtnValue TABLE
(
    RowID INT NOT NULL
)
AS
BEGIN
    WITH numbers AS
    (
        SELECT 1 as N
        UNION ALL
        SELECT N + 1
        FROM numbers 
        WHERE N + 1 <= @numRows
    )
    INSERT INTO @RtnValue
        SELECT N
        FROM numbers

    RETURN
END
GO

It works, but has a limit of recursion depth of 100, which is inappropriate for me. Can you suggest alternatives?

possible duplicate of [SQL, Auxiliary table of numbers](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers) — Martin Smith, Jan 17 '14 at 18:32
You can set a `maxrecursion` limit greater than 100 but cross joined CTEs are better. — Martin Smith, Jan 17 '14 at 18:34

score 3 · Accepted Answer · answered Jan 17 '14 at 18:33

always use the dbo. schema prefix when creating or referencing objects, especially functions.
you should strive to create inline table-valued functions, as opposed to multi-statement table-valued functions, when possible.
Recursive CTEs are about the least efficient way to generate a set (see this three-part series for much better examples):

Here is one example:

CREATE FUNCTION dbo.GenerateRows(@numRows INT = 1)
RETURNS TABLE
AS
  RETURN 
  (
    SELECT TOP (@numRows) RowID = ROW_NUMBER() OVER (ORDER BY s1.[number])
      FROM master.dbo.spt_values AS s1
      -- CROSS JOIN master.dbo.spt_values AS s2 
      ORDER BY s1.[number]
  );

If you need more than ~2,500 rows, you can cross join with itself, or another table.

Even better would be to create your own numbers table (again, see the links above for examples).

J0e3gan · Answer 2 · 2014-01-17T18:42:22.400

1

Don't think iteratively - looping - but set-based - all at once.

An INSERT INTO...SELECT TOP x… should do what you need without repeated inserts.

I will follow with an example when I'm not bound to my phone.

UPDATE:

What @AaronBertrand said. :} A CROSS JOIN in the SELECT is spot-on.

edited Jan 17 '14 at 18:42

answered Jan 17 '14 at 18:26

J0e3gan

8,740
10
53
80

`SELECT` from what? In order to get top `@n` rows I need some source of rows that already has `@n` rows or more. – n0rd Jan 17 '14 at 18:31

Generate result set of a certain length

2 Answers2