SQL - Creating a joined temp table

Question

I am looking to join two tables into a temp table, then use the temp table in a select query. This is the select statement for the temp table I want to create:

select program, event
from OMEGA.HP
inner join POM.GT
on program = substring(name,7,4)
where LENGTH(name)= 25

What is the easiest way to create a temp table for the above?

Answer 1

You can create a temp table (and you might want to), but if you don't know already, I want to point out you can create a virtual temp table in a query like this

SELECT * 
FROM (
  select program, event
  from OMEGA.HP
  inner join POM.GT on program = substring(name,7,4)
  where LENGTH(name)= 25
) AS Virtual_table

you could even join to another of these tables like this:

SELECT * 
FROM (
  select program, event
  from OMEGA.HP
  inner join POM.GT on program = substring(name,7,4)
  where LENGTH(name)= 25
) AS v_table74
join (
  select program, event
  from OMEGA.HP
  inner join POM.GT on program = substring(name,2,5)
  where LENGTH(name)= 25
) as v_table25 on v_table74.program = v_table25.program

Answer 2

select program, event
into #temp    --------------------------------check here
from OMEGA.HP
inner join POM.GT
on program = substring(name,7,4)
where LENGTH(name)= 25