Comparing Tables in SQL Server 2k

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 2000 Forums

SQL Server Development (2000)

Comparing Tables in SQL Server 2k

Author

Topic

Bobba Buoy
Starting Member

36 Posts

Posted - 2004-12-29 : 19:58:01

I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.

I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.

Thanks!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-29 : 21:43:12

[code]select * from <table1> t1
full outer join <table2> t2 on t2.partid = t1.partid and t2.raceid = t1.raceid
where t1.partid is null or t2.partid isnull[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 21:44:39

select *
from t1
full outer join t2
on t1.PartID = t2.PartID
and t1.RaceID = t2.RaceID
where t1.PartID is null
or t2.PartID is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-30 : 11:55:47

are you two telepathically linked together or what???

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-30 : 12:19:43

Check this out as well:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Neither of the other approaches mentioned will handle any nulls in your table.

- Jeff

Bobba Buoy
Starting Member

36 Posts

Posted - 2004-12-30 : 16:26:53

Thanks for your contributions. This has been very helpful!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources