CODEWITHSUNDEEP
sqlsql-server
carl brooks
carl brooks

Reputation: 15

increment number table - SQL

Just a simple question for a beginner in TSQL.

I just want to know how do I write a set base INSERT query for a number table where it just contain a list of numbers. The numbers may vary but for example if I want 5 numbers, the number table should output 20 rows with each row containing an increment number like so:

NumberID
1
2
3
4
5

Then lets say I decide to change the number to 7, it will display below:

NumberID
1
2
3
4
5
6
7

How do I do that?

Upvotes: 0

Views: 203

Answers (2)

Kaf
Kaf

Reputation: 33809

This is a recursive query using a CTE. Maximum possible number is 32767.

DECLARE @Number INT = 5

;WITH x AS
(
  SELECT num = 1
  UNION ALL
  SELECT num + 1 FROM x WHERE num < @Number
)
SELECT num FROM x
OPTION (MAXRECURSION 32767);

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Use row_number() function

select number from 
(
Select row_number() over (order by (select 0)) as number from sys.objects
) as t where number<=5

or

Select top 5 row_number() over (order by (select 0)) as number from sys.objects

Upvotes: 1

Related Questions