Creating a floating table to join to in a query

Question

In a database I'm missing a few basic tables that should provide the reference to a Type; these were hard-coded in the software. In order not to endlessly write case statements to change these values, I want to copy paste a simple floating table each time I need this data.

I'm completely new to the declare type statements, and so far no luck in creating a subquery I can paste into my scripts. The result should be like so:

| Type ID |   Period |
----------------------
|      1  |      day |
|      2  |     week |
|      3  |    month |
|      4  |  quarter |
|      5  |     year |

I simply want to declare this table as a subquery so I can join it on other tables containing type ID's

Answer 1

A temporary table is probably the simplest method because you can do this in one step:

select 1 as type_id, 'day' as period
into #temp_type_period union all
select 2 as type_id, 'week' union all
select 3 as type_id, 'month' union all
select 4 as type_id, 'quarter' union all
select 5 as type_id, 'year' ;

Using declare, you have to declare the table and then insert into it:

declare @type_period table (
    type_id int,
    period varchar(255)
);

insert into @type_period (type_id, period)
    select 1 as type_id, 'day' as period
    select 2 as type_id, 'week' union all
    select 3 as type_id, 'month' union all
    select 4 as type_id, 'quarter' union all
    select 5 as type_id, 'year' ;