-2

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.

Dale K
  • 16,372
  • 12
  • 37
  • 62
sye
  • 153
  • 3
  • 9
  • 2
    You can't create a temp table inside a view. Next... If you want the numbers 1-1000 inside a view consider using a tally table. – Dale K Mar 31 '21 at 21:27
  • 1
    You could use a CTE to generate the numbers but a materialized tally table would be better. – Dan Guzman Mar 31 '21 at 21:33
  • 3
    I note you haven't accepted a single answer to any of your questions. Is there a reason for this? If an answer helps you solve your problem you are expected to accept it. – Dale K Mar 31 '21 at 21:42
  • 2
    And you should be including sample data, expected results and your attempt. – Dale K Mar 31 '21 at 21:43
  • 2
    Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. – HABO Apr 01 '21 at 02:11

2 Answers2

1

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.

Stu
  • 3,766
  • 2
  • 3
  • 20
  • 1
    `DISTINCT` does make this a little more expensive that it needs to be, in my opinion. – Larnu Mar 31 '21 at 21:48
  • Yeah, it's only as I say a quick option, for a one -off. Using it to populate a permanent table as a one-off is best of course. – Stu Mar 31 '21 at 21:49
1

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;
Brian Stork
  • 666
  • 6
  • 14