Excluding Rows between 2 temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Excluding Rows between 2 temp tables

Author

Topic

mwdallas
Starting Member

4 Posts

Posted - 2014-10-27 : 16:20:08

I cannot figure out how to do this and I know it's probably very simple. I have 2 temp tables that I want to join: Table 1 is my full list and Table 2 is what I want to delete from Table 1. The results I want to insert into a permanent Table 3.

Here's is basically my code but I don't know what I need to exclude Table2 accts from Table1 and put it into Table3.

truncate table Table3

insert Table3
select
t1.ACCT#,
t1.ESCPY1,
t1.ESCPY2,
t1.ESCTYP,
t1.COVERG,
d1.PYECD1,
d1.PYECD2,
d1.PYENME
from #Table1 t1
inner join #Table2 d1
on t1.acct# = d1.acct#
and t1.esctyp = d1.esctyp
and t1.escpy1 = d1.pyecd1
????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 17:45:00

I'm not clear what should go into Table3 based on your wording. Could you show us a quick data example for all 3 tables, meaning what's in Table1, what's in Table2 and what should go into Table3?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jkrusic
Starting Member

3 Posts

Posted - 2014-10-28 : 15:47:15

You could do the following for example:

Select
t1.ACCT#,
t1.ESCPY1,
....

into Table3

from #Table1 t1
where
t1.ACCT# NOT IN (Select ACCT# from #Table2)

So basically this takes ALL (#Table1) and removing everything that is similar to some (#table2)

EDIT:
If you are joining #table1 and #table2, this will result everything you want to delete essentially. Reason I say this is because you are joining on the ACCT# for both the temp tables, so this will result in everything that is alike between the two. You could do a left join from #table1 to #table2 which will result in NULL's where the data does not match.

mwdallas
Starting Member

4 Posts

Posted - 2014-10-29 : 10:07:58

Ok to clarify what I am doing...here is the full code. See notes at bottom... (THANK YOU FOR YOUR HELP!)
-----------------------------

if object_id('tempdb..#TEMP1','u') is not null
begin
drop table #TEMP1
end

select *
INTO #TEMP1
from FIRSTPULL
where TTYPE in(40,41,43,44,45,46,48)

-- select count(*) from #TEMP1 -- 993,549
/*===========================================================================
300 Select Accts to REMOVE from initial list
=============================================================================*/
DROP TABLE #DELETES

CREATE TABLE #DELETES
(
[ACCT#] [bigint] NOT NULL,
[ESCICR] [tinyint] NOT NULL,
[ESCAMT] [decimal](9, 2) NOT NULL,
[ESCDES] [varchar](23) NOT NULL,
[OPTION] [char](1) NOT NULL,
[ESCPY1] [smallint] NOT NULL,
[ESCPY2] [int] NOT NULL,
[ESCSTS] [tinyint] NOT NULL,
[TTYPE] [smallint] NOT NULL,
[ESCFRQ] [smallint] NOT NULL,
[CTNPOL] [char](1) NOT NULL,
[RNWLRX] [decimal](4, 3) NOT NULL,
[SJE002] [varchar](2) NOT NULL,
[STCODE] [smallint] NOT NULL,
[COVERG] [int] NOT NULL,
[ESCDDT] [int] NOT NULL,
[ESCEXP] [int] NOT NULL,
[PYECD1] smallint NOT NULL,
[PYECD2] int NOT NULL,
[PYENME] varchar(50) NOT NULL
)

INSERT INTO #DELETES
select
t1.ACCT#,
t1.ESCICR,
t1.ESCAMT,
t1.ESCDES,
t1.[OPTION],
t1.ESCPY1,
t1.ESCPY2,
t1.ESCSTS,
t1.TTYPE,
t1.ESCFRQ,
t1.CTNPOL,
t1.RNWLRX,
t1.SJE002,
t1.STCODE,
t1.COVERG,
t1.ESCDDT,
t1.ESCEXP,
s1.PYECD1,
s1.PYECD2,
s1.PYENME
from #TEMP1 t1
inner join DELETES_TABLE s1
on t1.ESCPY1 = s1.PYECD1 and t1.TTYPE = s1.TTYPE
WHERE (t1.ESCPY2 <> 6021 AND t1.ESCPY1 = 602)
OR t1.ESCPY1 <> 602

/*===========================================================================
400 Remove Closed Accts from original list = FINAL LIST
=============================================================================*/
truncate table FINAL_TABLE

Select
t1.ACCT#,
t1.ESCICR,
t1.ESCAMT,
t1.ESCDES,
t1.[OPTION],
t1.ESCPY1,
t1.ESCPY2,
t1.ESCSTS,
t1.ESCTYP,
t1.ESCFRQ,
t1.CTNPOL,
t1.RNWLRX,
t1.SJE002,
t1.STCODE,
t1.COVERG,
t1.ESCDDT,
t1.ESCEXP,
d1.PYECD1,
d1.PYECD2,
d1.PYENME
into FINAL_TABLE
from #TEMP1 t1
where
t1.ACCT# NOT IN (Select ACCT# from #DELETES)

ERROR:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "d1.PYECD1" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "d1.PYECD2" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "d1.PYENME" could not be bound.

Notes:

(1) I have to be sure that I delete the Accts from #DELETES which have this criteria:
t1.acct# = d1.acct#
and t1.esctyp = d1.esctyp
and t1.escpy1 = d1.pyecd1

(2) the FINAL_TABLE does not allow NULL in any column
(3) There could be multiple rows for one account in the #DELETES

mwdallas
Starting Member

4 Posts

Posted - 2014-10-29 : 10:11:57

Sorry where you see ESCTYP, it should be TTYPE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources