asked on 6/15/2021

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.

Microsoft SQL Server

Last Comment

Scott Pletcher

6/15/2021

ASKER CERTIFIED SOLUTION

David Todd

6/15/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

RUA Volunteer2?

6/15/2021

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

6/15/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Scott Pletcher

6/15/2021

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.