How to write this script without use of temp tables

Question

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

Hint: WITH. Common table expressions can directly replace your temporary tables (although the names would not start with #). — Gordon Linoff
– Gordon Linoff, Commented Jan 19, 2017 at 15:25
Why do you think temp tables should be avoided? Avoiding them sounds like a misguided (or perhaps misunderstood) statement. — Sean Lange
– Sean Lange, Commented Jan 19, 2017 at 15:33
@SeanLange, I am not actually sure where I heard that. Maybe I am thinking of cursors or something else. — Greg Gum
– Greg Gum, Commented Jan 19, 2017 at 15:37
Cursors should be avoided whenever possible (which is almost always) but temp tables do not share the same performance challenges that cursors do. But in the example you posted I would probably choose to utilize a cte instead of temp tables because they are easier to work with in this situation. — Sean Lange
– Sean Lange, Commented Jan 19, 2017 at 15:42

Greg Gum · Accepted Answer · 2017-01-19 15:46:15Z

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

Collectives™ on Stack Overflow

How to write this script without use of temp tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related