Hier is een recursieve oplossing. Geinspireerd op de code van deze site: http://mhimu.wordpress.com/2009/05/07/sql-dynamic-date-range-number-range-in-oracle-sql-server-and-db2400/. Daar doet de schrijver het met dateranges.
NB: Recursie kent een grens. In SQL Server default 100 stappen, met OPTION (MAXRECURSION n) kun je een andere waarde kiezen. De Books Online over MAXRECURSION:
- MAXRECURSION number
-
Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.
Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.
For more information, see WITH common_table_expression (Transact-SQL).
In dit voorbeeld maak ik er een tabelletje met users mee, maar je kunt zelf vast ook andere opties verzinnen.
WITH IntRange (i) AS
(
SELECT 1 as i
UNION ALL
SELECT (1 + i) as i FROM IntRange WHERE i < 150
)
SELECT i from IntRange
OPTION (MAXRECURSION 150)
GO
WITH IntRange (i) AS
(
SELECT 1 as i
UNION ALL
SELECT (1 + i) as i FROM IntRange WHERE i < 150
)
SELECT i as ID, 'user_' + CONVERT(varchar(3), i) as Username, 'password_' + CONVERT(varchar(3), i) as Pwd
INTO #users
from IntRange
OPTION (MAXRECURSION 150)
GO
SELECT * FROM #users
GO
