asked on

How do I create a temporary table that will omit specific values

I think the format is something like this:
if object_id('tempdb..#special_types') is not null drop table #special_types
create table #special_types
(
special_type_code varchar(100) collate database_default
)
insert into #special_types select value from dbo.c_split(@special_type_code,',')

The Special Codes that need to be omitted are. Where would this go.
" tbl.special_code not in ('192','703','A95','705','A955','7054') "

I am assuming this table would then need to be joined in the FROM statement to pull those records back in.

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUA Volunteer2?

ASKER

I thought it would have been something like this since the format below seems to be the way other developers I work with expect it. I have a long series of these temp tables " if object_id('tempdb..# none of them omit values.

if object_id('tempdb..#special_types') is not null
AND tbl.special_code not in ('192','703','A95','705','A955','7054')
drop table #special_types create table #special_types
(
special_type_code varchar(100) collate database_default
)
insert into #special_types select value from dbo.c_split(@special_type_code,',')

SOLUTION

Andrei Fomitchev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

It works quicker than #tmp because TABLE-VARIABLE located in-memory and does not require disk I/O.

False. There is no difference between #table and @table as far as memory / disk usage goes. @table can go to disk just like #table can.

If you think about it, this must be true. What if you put a 200M rows into an @table? Would SQL allow itself to crash because it tried to force it all into RAM? Of course not. It would spool it out to disk, just like it does with #tables.