CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Greg Gum
Greg Gum

Reputation: 37909

How to write this script without use of temp tables

I am a C# developer learning SQL. I wrote the following scrip which works using temp tables.

But, as I understand it, temp tables are to be avoided.

Why should they not be used?

How should I rewrite this to not use temp tables?

To explain the sql, it gathers data from two different tables, then joins those two temp tables together and compares them. It basically finds the records in the first temp table that are not in the second one.

 if object_id('tempdb..#pa') is not null
drop table #pa



Select dm.document_code, sm.segment_code
into #pa

from [dbo].[metadata_document_set] ds
join [dbo].[document_metadata] dm
on dm.metadata_document_set_id = ds.metadata_document_set_id

join segment_metadata sm
on dm.document_metadata_id = sm.document_metadata_id

where ds.code = 'PA'
and dm.code <> 'TEST'
and sm.not_in_spec = 0


if object_id('tempdb..#oh') is not null
drop table #oh


Select dm.document_code, sm.segment_code
into #oh

from [dbo].[metadata_document_set] ds

join [dbo].[document_metadata] dm
on dm.metadata_document_set_id = ds.metadata_document_set_id

join segment_metadata sm
on dm.document_metadata_id = sm.document_metadata_id

where ds.code = 'OH'
and dm.code <> 'TEST'
and sm.not_in_spec = 0


select * from #oh oh
left join #pa pa
on pa.segment_code = oh.segment_code and
pa.document_code = oh.document_code
where pa.document_code is null
order by oh.document_code, oh.segment_code

Upvotes: 0

Views: 160

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use CTE or subquery.

instead of 

Select dm.document_code, sm.segment_code
into #pa
from [dbo].[metadata_document_set] ds

use WITH to create the subquery

WITH temporalPA as (

   Select dm.document_code, sm.segment_code
   from [dbo].[metadata_document_set] ds
)
SELECT *
FROM temporalPA

You also can use the subquery directly, but isnt as easy to read.

SELECT *
FROM ( Select dm.document_code, sm.segment_code
       from [dbo].[metadata_document_set] ds
     ) as temporalPA

To add the full rewrite to this answer:

--Return all segments which are in OH, but not PA
with tempPA as (
  Select dm.document_code, sm.segment_code
    from [dbo].[metadata_document_set] ds
    join [dbo].[document_metadata] dm
    on dm.metadata_document_set_id = ds.metadata_document_set_id

    join segment_metadata sm
    on dm.document_metadata_id = sm.document_metadata_id

    where ds.code = 'PA'
    and dm.code <> 'TEST'
    and sm.not_in_spec = 0
),

tempOH as(
Select dm.document_code, sm.segment_code
from [dbo].[metadata_document_set] ds

join [dbo].[document_metadata] dm
on dm.metadata_document_set_id = ds.metadata_document_set_id

join segment_metadata sm
on dm.document_metadata_id = sm.document_metadata_id

where ds.code = 'OH'
and dm.code <> 'TEST'
and sm.not_in_spec = 0

)

select * from tempOH oh
left join tempPA pa
on pa.segment_code = oh.segment_code and
pa.document_code = oh.document_code
where pa.document_code is null
order by oh.document_code, oh.segment_code

Upvotes: 3

Related Questions