difference between two database 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

General SQL Server Forums

New to SQL Server Programming

difference between two database tables

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2011-09-23 : 16:10:42

I have two queries

query1

select IDW_SITE_NAME REGION,a.location_sid,a.CALENDAR_SID,count(*) from idw.vod_stream a, IDW_REF_DATA b
where a.location_sid = b.location_sid and a.calendar_sid between 20110901 and 20110915 --this is start date parameter and end date parameter
and a.location_sid = 190 --this is location id parameter
and HAS_ERROR = 'N'
group by IDW_SITE_NAME,a.location_sid,a.CALENDAR_SID
order by a.location_sid,a.CALENDAR_SID

output

REGION LOCATION_SID CALENDAR_SID IDW COUNT
AUSTIN 190 20110901 110557
AUSTIN 190 20110902 132851
AUSTIN 190 20110903 168656

query2

select REGION,a.location_sid,a.CALENDAR_SID,count(*) from sda_vod_stream a, SDA_DIVISIONS b
where a.location_sid = b.location_sid and a.calendar_sid between 20110901 and 20110915
and a.location_sid = 190
group by REGION,a.location_sid,a.CALENDAR_SID
order by a.location_sid,a.CALENDAR_SID

output

REGION LOCATION_SID CALENDAR_SID SDA COUNT
AUSTIN 190 20110901 110559
AUSTIN 190 20110902 132851
AUSTIN 190 20110903 168659

both queries will be getting executed on separate database
e.g
query1-->executes on database1
query2-->executes on database2

now my requirement is that

here is final output
REGION LOCATION_SID CALENDAR_SID IDW COUNT SDA COUNT DIFF
AUSTIN 190 20110901 110557 110559 -2
AUSTIN 190 20110902 132851 132851 0
AUSTIN 190 20110903 168656 168659 -3
here difference = query1.count - query2.count

how can I produce a final output CSV /XLS file
I can generate above report file using oracle,but here both databases are different
I'm thinkning to copy above SQL in a shell script and can generate a report as oracle is based on UNIX

parameters
in above both query the changing parameter is
1. 20110901 this is start date parameter
2. 20110915 end date parameter
3. 190 this is location id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-24 : 04:52:23

you can use linked server for that. Add linked server for oracle db in sql server and then use [linkedserver].db..tablename for oracle tables in sql server and you can join to sql server table on REGION, LOCATION_SID, CALENDAR_SID combination. To get final output in CSV you can use bcp or OPENROWSET.
Another way to get it is using SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources