How to find missing records comparing 2 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 2005 Forums

Analysis Server and Reporting Services (2005)

How to find missing records comparing 2 tables

Author

Topic

tahseenm
Yak Posting Veteran

64 Posts

Posted - 2012-10-18 : 08:30:19

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 08:56:04

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

tahseenm
Yak Posting Veteran

64 Posts

Posted - 2012-10-18 : 08:57:30

Let me try it and let you know soon. Thanks

moetahsen

tahseenm
Yak Posting Veteran

64 Posts

Posted - 2012-10-18 : 09:22:38

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 10:07:06

[code]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[/code]

tahseenm
Yak Posting Veteran

64 Posts

Posted - 2012-10-18 : 10:24:24

Great JOb I got it. Thanks a million and take care.

moetahsen

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 10:43:37

Glad to help .)

tahseenm
Yak Posting Veteran

64 Posts

Posted - 2012-10-18 : 10:45:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources