MS SQL Tips & Tricks

How to get a table of X numbered records

The simpliest way to get a table of 100 records numbered 0-99 is to build up a query as bellow. It is easy to extend the amount of records by adding next join sections. 

select pm1.Digit+pm2.Digit*10 as Number
from(
	select cast(1 as int) as Digit
	union select 2
	union select 3
	union select 4
	union select 5
	union select 6
	union select 7
	union select 8
	union select 9
	union select 0
	) as pm1
inner join (
		select cast(1 as int) as Digit
			union select 2
			union select 3
			union select 4
			union select 5
			union select 6
			union select 7
			union select 8
			union select 9
			union select 0
	)  as pm2 on (1=1)

See also


Learn more tricks

Add Comment

Comments