1

To Declare a list of integers in sql, we use the following code

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);

However I would like to declare a long list from 1 to n. Is it possible to finish this in one command without using loop?

declare @listOfIDs table (id int);
insert @listOfIDs(id) values  ????From 1 to n?????
Chow Stanley
  • 61
  • 5
  • did you check https://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers ? – demo Aug 18 '21 at 09:21

1 Answers1

0

If you use sql-server, then you can use any table to generate the consecutive integer (it depends on how many rows on your table).

Many people use master table master.dbo.spt_values to generate consecutive integer because it contains around 2xxx rows.

If you are using Cross Join, then you can generate 2xxx * 2xxx rows. For example : 2000 * 2000 = 4,000,000 rows. That means you can generate 4,000,000 consecutive integer

declare @listOfIDs table (id int);

insert @listOfIDs(id) 
SELECT Top (2000) Row_Number() Over (Order By (Select 1)) 
FROM master.dbo.spt_values AS s1
CROSS JOIN master.dbo.spt_values AS s2

select * from @listOfIDs

You can change Top(2000) to Top(n) where n is maximum number

GGG
  • 486
  • 4
  • 9