Method for Comparing Two Disparate 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 2008 Forums

Transact-SQL (2008)

Method for Comparing Two Disparate Tables...

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2012-11-19 : 14:40:02

I have two separate databases (located in the same facility). Each has a list of contacts. I need to ensure that these lists match on occasion.
So, I have something like:
Database A / Database B
I want to do something like (pseudo):
Select All From A.Contact And From B.Contact,
Return All Where A.Contact.ID IS NOT IN B.Contact.ID
Any thoughts on how to accomplish this?
Thanks,
Dave

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 14:43:18

You can use linked server or export that table to other server as temporary table and then you can compare.

If it is in same server:

Use this :

Select T.* 
from [DatabaseA].dbo.Contact T
Where NOT EXISTS ( SELECT * from [DatabaseB].dbo.Contact Where ID = T.ID)

davidshq
Posting Yak Master

119 Posts

Posted - 2012-11-19 : 15:01:40

Thanks. This is very helpful. Here is what my code looks like now:

select MS_MSCRM..ContactExtensionBase, MyDB..My_Prospects
from My_MSCRM..ContactExtensionBase CEB
Where NOT EXISTS (SELECT * from MyDB..Prospects IRFP Where IRFP.dynamicsID=CEB.new_ContactIdentifier)

I'm running into ~~two~~ one issue:
~~1. It states that it "Cannot resolve the collation conflict between 'Latin1_General_CI_AI' and 'SQL_Latin_General_CP1_CI_AS' in the equal to operation."~~ (Used COLLATE DATABASE_DEFAULT to rectify).
2. It is also complaining about "multi-part identifiers" - which I know is usually related to not aliasing the tables - but I did?
Thanks,
Dave

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 15:16:03

use the table alias instead of the 3-part name

SELECT CEB.ContactExtensionBase,
       CEB.My_Prospects
FROM   My_MSCRM..ContactExtensionBase CEB
WHERE  NOT EXISTS (
           SELECT *
           FROM   MyDB..Prospects IRFP
           WHERE  IRFP.dynamicsID = CEB.new_ContactIdentifier
       )

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 15:17:10

Sorry do you mean you want to compare both ways? If yes you can use Full join to get it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources