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 2008 Forums
 Transact-SQL (2008)
 Method for Comparing Two Disparate Tables...		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidshq
Posting Yak Master

USA
119 Posts
Posted - 11/19/2012 :  14:40:02  Show Profile  Visit davidshq's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/19/2012 :  14:43:18  Show Profile  Reply with Quote
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)
Edited by - sodeep on 11/19/2012 15:16:19
Go to Top of Page

davidshq
Posting Yak Master

USA
119 Posts
Posted - 11/19/2012 :  15:01:40  Show Profile  Visit davidshq's Homepage  Reply with Quote
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
Edited by - davidshq on 11/19/2012 15:11:47
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/19/2012 :  15:16:03  Show Profile  Reply with Quote
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
       )
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/19/2012 :  15:17:10  Show Profile  Reply with Quote
Sorry do you mean you want to compare both ways? If yes you can use Full join to get it.
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.05 seconds. Powered By: Snitz Forums 2000