SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 How to find missing records comparing 2 tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tahseenm
Yak Posting Veteran

USA
55 Posts
Posted - 10/18/2012 :  08:30:19  Show Profile  Reply with Quote
I am using Sql Server 2005 and I have 2 tables to compare. Table1 has all the account#'s with 28,500 records and the Table2 has the account#'s also but less records about 27,800 records comparing to Table1. So I want to write a script to show me the missing records in table 2 when I am comparing with Table1. I really appreciate if someone could show me the correct script to get the data which is missing in table 2. Thanks and appreciated.

moetahsen

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/18/2012 :  08:56:04  Show Profile  Reply with Quote
Use one or the other below depending on your requirements:
SELECT accountNumber FROM Table1 
EXCEPT
SELECT accountNumber FROM Table2

SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2
Go to Top of Page

tahseenm
Yak Posting Veteran

USA
55 Posts
Posted - 10/18/2012 :  08:57:30  Show Profile  Reply with Quote
Let me try it and let you know soon. Thanks

moetahsen
Go to Top of Page

tahseenm
Yak Posting Veteran

USA
55 Posts
Posted - 10/18/2012 :  09:22:38  Show Profile  Reply with Quote
Thanks for the quick reply. Here are the details that I forgot to mention to you that the Table1 name is AssetManagement_Water.Compwmtr where I get all the Unitid i.e account#'s and the script below is the results where i get all the unitid but missing some of them which I call as Table2. So my question is that when I compare Table1 i.e. AssetManagement_Water.Compwmtr with the results I get from the script below i.e. I call Table2. So I need to know what unitid I am missing from Table1 comparing to the results I am getting from Table2 i.e. the script below. I hope this helps you out to figure out what I am looking for. Thanks for your reply.


select UnitID,Compwmtr.Position, RouteID, Seqno
From AssetManagement_Water.Compwmtr,Metermanagement_Water.RTADDR,Metermanagement_Water.Route
where Compwmtr.Addrkey = RTADDR.Addrkey and RTADDR.Routekey = Route.Routekey and RTADDR.Position = Compwmtr.Position
Order by RouteID DESC


moetahsen
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/18/2012 :  10:07:06  Show Profile  Reply with Quote 
SELECT Unitid FROM AssetManagement_Water.Compwmtr
EXCEPT
SELECT UnitID --,
       --Compwmtr.Position,
       --RouteID,
       --Seqno
FROM   AssetManagement_Water.Compwmtr,
       Metermanagement_Water.RTADDR,
       Metermanagement_Water.Route
WHERE  Compwmtr.Addrkey = RTADDR.Addrkey
       AND RTADDR.Routekey = ROUTE.Routekey
       AND RTADDR.Position = Compwmtr.Position
Go to Top of Page

tahseenm
Yak Posting Veteran

USA
55 Posts
Posted - 10/18/2012 :  10:24:24  Show Profile  Reply with Quote
Great JOb I got it. Thanks a million and take care.

moetahsen
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/18/2012 :  10:43:37  Show Profile  Reply with Quote
Glad to help .)
Go to Top of Page

tahseenm
Yak Posting Veteran

USA
55 Posts
Posted - 10/18/2012 :  10:45:17  Show Profile  Reply with Quote
Just want to let you know that this site is excellent and I always get my solution very fast and again thanks for doing a great job. Keep it up and talk to you later.

moetahsen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000