CODEWITHSUNDEEP
sqlinner-jointemp-tables
rcianfar
rcianfar

Reputation: 61

SQL - Creating a joined temp table

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?

Upvotes: 6

Views: 46792

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Use a Common Table Expression (CTE):

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

Upvotes: 2

Hogan
Hogan

Reputation: 70513

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

Upvotes: 7

LONG
LONG

Reputation: 4610

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

Upvotes: 2

Related Questions