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.
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 BI 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.IDAny 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 TWhere 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_Prospectsfrom My_MSCRM..ContactExtensionBase CEBWhere 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 nameSELECT CEB.ContactExtensionBase, CEB.My_ProspectsFROM My_MSCRM..ContactExtensionBase CEBWHERE 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. |
|
|
|
|
|