Some tasks I do in SQL Server would be much simpler if I could get a sequence or a range of numbers. I could create users#1 to user#1000 without any programming – just by using plain old SQL. PostgreSQL offers the generate_series function for such use cases. Unfortunately, there is nothing comparable in SQL Server. All you can find on Google are solutions that are complicated, long and impossible to remember.
The one exception I found was from johnqflorida in his answer in the SQL Server forums. His solution is short and works with large ranges. You can use this query to get the numbers from 1 to 100’000 – each number in its own row:
|
1 2 3 |
SELECT TOP 100000 ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY A.name) AS 'Range' FROM syscolumns A, syscolumns B |
With a little modification, you can get any range you need (50 to 250):
|
1 2 3 |
SELECT TOP 200 50 + ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY A.name) AS 'Range' FROM syscolumns A, syscolumns B |
To create Users with a number in their name, you can combine the range from above with your INSERT statement:
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO [dbo].[Users] ([FirstName] ,[LastName] ) SELECT TOP 100 'BDD', 'User#'+ CONVERT(nvarchar(4), 2000 + ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY A.name)) FROM syscolumns A, syscolumns B |
This approach gives me up to 16 million values before SQL Server runs into an out of memory exception. I may never need such a big range, but it is great to finally have a solution that can produce a large range of numbers without falling back to writing my own procedures or complicated statements.