Question on Temp Tables

[osirisa]

Does anyone know how to compare two temp tables using a left outer join? I have to find the diferences between Temp Table A and Temp Table B.

I tried the following Select statement but is incorrect. I will apreciate if anyone can help me out in the comparison of the two temp tables.

Thanks!!!!


Select a.station_nbr, a.station_name,a.group_code, a.beg_eff_date_DGC, a.end_eff_date_DGC,
b.beg_eff_date_SES, b.end_eff_date_SES
INTO #TP_DGC_vs_SES
From #TP_DGC a left outer join #TP_SES b
on a.station_nbr=b.station_nbr and a.group_code = b.group_code
#TP_SES c left outer join #TP_DGC d
on c.station_nbr=d.station_nbr and c.group_code = d.group_code
WHERE (a.group_code = b.group_code)
and(a.station_nbr = b.station_nbr)
and (b.station_nbr is not Null)



Thank You in advance for your help!!!!!!

 

[kaht]

use a full outer join and only pull the results that contain nulls:

[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] ([COLOR=blue]value[/color] [COLOR=blue]int[/color])
[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] ([COLOR=blue]value[/color] [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a
[COLOR=blue]select[/color] 1 union
[COLOR=blue]select[/color] 2 union
[COLOR=blue]select[/color] 3 union
[COLOR=blue]select[/color] 4 union
[COLOR=blue]select[/color] 5

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b
[COLOR=blue]select[/color] 3 union
[COLOR=blue]select[/color] 4 union
[COLOR=blue]select[/color] 5 union
[COLOR=blue]select[/color] 6 union
[COLOR=blue]select[/color] 7

[COLOR=blue]select[/color] *
[COLOR=blue]from[/color] @a a
   [COLOR=blue]full[/color] [COLOR=blue]join[/color] @b b
   [COLOR=blue]on[/color] a.value = b.value
[COLOR=blue]where[/color] a.value [COLOR=blue]is[/color] null
   or b.value [COLOR=blue]is[/color] null

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[osirisa]

Thank you kaht appreciate it !!!

 

[osirisa]

/* Create a Temp Table */

Create Table #TP_DGC(
station_nbr char (20),
station_name varchar (40),
group_code char (12),
beg_eff_date_DGC datetime,
end_eff_date_DGC datetime)


--------------Selection Gathering Table-------------------


DECLARE @groupcode char (12),
@stationnumber char(20),
@stationame varchar(40),
@begdateG datetime,
@endeffdateG datetime,
@Records int,
@record_found_flag char(1),
@current_date datetime



Insert Into #TP_DGC (station_nbr, station_name, group_code, beg_eff_date_DGC, end_eff_date_DGC)
SELECT Distinct a.station_nbr, c.station_name, a.group_code,
a.beg_eff_date As beg_eff_date_DGC, a.end_eff_date As End_eff_date_DGC
from TIES_Gathering.dbo.station_group a
Inner Join TIES_Gathering.dbo.station c on a.station_nbr = c.station_nbr
Inner Join TIES_Gathering.dbo.station_type d on a.station_nbr = d.station_nbr
Inner Join TIES_Gathering.dbo.group_detail e on a.group_code = e.group_code
Where ((e.group_type = 'NPN')or(e.group_type = 'WTF')or(e.group_type = 'NTF'))and
((d.type_code = 'WHM')or (d.type_code = 'CRP')) and (a.pipeline_code = '100000')
Order by a.station_nbr, a.group_code

--Select*
--From #TP_DGC



--------------Selection Marketing Table-------------------


Create Table #TP_SES(
station_nbr char (20),
station_name varchar (40),
group_code char (12),
beg_eff_date_SES datetime,
end_eff_date_SES datetime)



DECLARE @groupcode_M char (12),
@stationnumber_M char(20),
@stationame_M varchar(40),
@begdateM datetime,
@endeffdateM datetime,
@Records_M int,
@record_found_flag_M char(1)




Insert Into #TP_SES (station_nbr, station_name, group_code, beg_eff_date_SES, end_eff_date_SES)
Select Distinct a.station_nbr,b.station_name,a.group_code, a.beg_eff_date As beg_eff_date_SES, a.end_eff_date As

End_eff_date_SES
From TIES_Marketing.dbo.station_group a
Inner Join TIES_Marketing.dbo.station b on a.station_nbr = b.station_nbr
Inner Join TIES_Marketing.dbo.station_type c on a.station_nbr = c.station_nbr
Inner Join TIES_Marketing.dbo.group_detail e on a.group_code = e.group_code
Where ((c.type_code = 'WHM')or (c.type_code = 'CRP')) and (a.pipeline_code = '100001')
Order by a.station_nbr, a.group_code


--SELECT *
--From #TP_SES




---------------compare both tables--------------------


Select a.station_nbr, a.station_name,a.group_code, a.beg_eff_date_DGC, a.end_eff_date_DGC,
b.beg_eff_date_SES, b.end_eff_date_SES
INTO #TP_DGC_vs_SES
From #TP_DGC a left outer join #TP_SES b
on a.station_nbr=b.station_nbr and a.group_code = b.group_code
#TP_SES c left outer join #TP_DGC d
on c.station_nbr=d.station_nbr and c.group_code = d.group_code
WHERE (a.group_code = b.group_code)
and(a.station_nbr = b.station_nbr)
and (b.station_nbr is not Null)


--SELECT station_nbr,station_name,group_code,beg_eff_date_DGC,end_eff_date_DGC
--From #TP_DGC_vs_SES

--Select A.station_nbr, A.station_name, A.group_code, A.beg_eff_date_SES, A.end_eff_date_SES
--INTO #TP_DGC_vs_SES
--From #TP_SES a left outer join #TP_DGC b
--on a.station_nbr=b.station_nbr
--and a.group_code = b.group_code
--and b.station_nbr is not Null



-- drop temporary tables---
drop table #TP_DGC
drop table #TP_SES
GO



Am I close?