I need to create a temporary table inside of a view that has one column with number one to 1000. Any ideas on how to do this?
I am using SQL Server.
Thanks.
I need to create a temporary table inside of a view that has one column with number one to 1000. Any ideas on how to do this?
I am using SQL Server.
Thanks.
The best way is to have a permanent table of digits available, it has so many uses.
For a quick and dirty on-the-fly you can do
select distinct number from
master..spt_values
where number between 1 and 1000
order by number
You'd be better off just inserting into a permanent table though for reuse
select distinct Number
into utils.Digits /* utils schema for example only */
from
master..spt_values
where number between 1 and 1000
And creating a unique clustered index on it.
The tally table solution is best (because of the index), but if you don't have one and need one, you can use a Common Table Expression in a pinch.
I normally use a ROW_NUMBER() and the sys.columns table. If I need LOTS of rows, I cross join back to sys.columns.
create view dbo.SomeView as
with Tally as (
select top(1000) row_number() over (order by A.object_id) as N
from sys.columns A
cross join sys.columns B -- If you need LOTS of rows, you add more cross joins
)
select *
from something
join Tally on Tally.N = something.N;