sql - How to get rid of #temp tables from the query

Question

I have three queries that put data in their respective #temp tables. Later, In one query those #temp tables are used again to get the data.

i.e

select {some columns} into #temp1 from {some tables} where {conditions1}
select {some other columns} into #temp2 from {some tables} where {conditions2}
select {some other columns} into #temp3 from {some tables} where {conditions3}

select {some columns from all #temp tables} from #temp1, #temp2, #temp3 where {conditions}

I want to get rid of these #temp tables and want to run last query without those #temp tables.

Any thought!!!

How about writing three different functions and putting all three query in those functions and calling functions from the third query!!

Thanks

Jai

Answer 1

if you are using MSSql, use CTE

;with cte1 as (
    select {some columns} from {some tables} where {conditions1}
), 
cte2 as (
    select {some other columns} from {some tables} where {conditions2}
),
cte3 as (
    select {some other columns} from {some tables} where {conditions3}
)
select {some columns from all ctes} from cte1, cte2, cte3 where {conditions}

this should run faster as there is no need to insert data into temp table.

another advantage of avoiding temp table is, using temp table sometimes has really bad impact on performances, as there is only one tempdb for entire sql server and heavily use tempdb, may block other queries. just google temp table and performance impacts, you will find a lots of article on this topic