Please review my SP

[osirisa]

_____________

Ok. I created the two temp tables and Now I am trying to compare the temp tables for discrepancies. Please review my SP and let me know if I am even close to what I want to do. What's the correct syntax to compare the two temp tables.

Thanks!!!!!!
__________________________________________________




/* 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

 

[kaht]

What's the correct syntax to compare the two temp tables

osirisa, I already showed you how to do this using a full join here:

thread183-1424060

-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]

Do I need a variable for the temp table?

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 full outer join #TP_SES b
on a.station_nbr=b.station_nbr or a.group_code= b.group_code
WHERE (a.station_nbr is Null)
or (b.station_nbr is Null)
and (a.group_code <> b.group_code)

-------

This is similar to your respond without the variable name declaration for the table. Do I need the variable declaration?
The Selection is not pulling all the records that have discrepancies.

Thank you kaht --- I am pretty new to sql. I started to using T-SQL 2000 in a project 4 weeks ago. I feel that there is a lot to learn.

 

[gmmastros]

Is this a school project?

Also, when there are several columns to compare, you may be better off using the CheckSum or binary_Checksum function(s).

Here is an example of the checksum function. It appears to be a little more complex than kaht's method, but when there are multiple columns to compare, it is actually a little easier.

Create Table #T1(Id Int, Data VarChar(20))
Create Table #T2(Id Int, Data VarChar(20))

Insert Into #T1 Values(1, 'Red')
Insert Into #T1 Values(2, 'Blue')


Insert Into #T2 Values(1, 'Red')
Insert Into #T2 Values(2, 'Green')

Select *
From   (
       Select Checksum(*) As T1CheckSum, *
       From   #T1
       ) As Table1
       Full Outer Join (
         Select Checksum(*) As T2CheckSum, *
         From   #T2
         ) As Table2
         On Table1.T1CheckSum = Table2.T2CheckSum
Where  Table1.Id Is NULL Or Table2.Id Is NULL

Also.... your current query has an OR in the join, I suggest you change that to AND as run it again.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom