compare to tables - SQL Server Forums

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

Transact-SQL (2000)

compare to tables

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-01 : 05:11:26

I have 2 tables in 2 different databases that should have the same records (minus the identity)

How can I compare the 2 tables (in 2 different db's ) to only see records that are not in both?

Is there any easy query to do this?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-03-01 : 09:38:36

select * from db1.dbo.table1 a
inner join db2.dbo.table2 b
on a.datacol1 = b.datacol1
and a.datacol2 = b.datacol2
and a.datacol3 = b.datacol3
....
and a.datacoln = b.datacoln
where a.datacol1 is null
union
select * from db2.dbo.table2 a
inner join db1.dbo.table1 b
on a.datacol1 = b.datacol1
and a.datacol2 = b.datacol2
and a.datacol3 = b.datacol3
....
and a.datacoln = b.datacoln
where a.datacol1 is null

or look to use Red-Gate.COM (Data Compare) technology for a more professional/repeating version.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-01 : 10:17:14

Try this:

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

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources