asked on

Query two databases for duplicate entries

I have two SQL databases. One is for Candidates and one is our Main database. We have a sp that transfers a Candidate into our Main database once we make contact with them and then deletes them from the Candidate database. Problem is, our sp to delete from the Candidate database is not working - so we have duplicate entries in both databases.

I need a query so I can look for duplicates in both databases.
Database 1 - CandSource Table - Contact1
Database 2 - Goldmine Table - Contact1

output to delimited file.
I know this is easy for a SQL god...but I am not one...
Thank you thank you.

ASKER CERTIFIED SOLUTION

soco180

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NetzillaKB

ASKER

the duplicate would be the name only. Frequently, while making contact with the candidate, the recruiter updates their city,state, or zip and then they wouldn't be duplicates.

Thanks. I'll see if this works...KB

NetzillaKB

ASKER

I had to tweak the table names a little, but I'm getting an output to the screen. How can I see which database the entry CAME from? And how can I get the output to a delimited file?

soco180

Another question, do you have a Primary Key such as an Identity column on the Candidate DB? It might be easier for you to script you Delete statement.

As for the delimited part: Are you using MS SQL Query Analyzer or MS SQL Server Managment Sudio 2005 or something else?

I tried to clear that up below by only selecting contacts from you Candidate database (which are also in the GoldMine DB).

SELECT C.ContactLastName + ',' + C.ContactFirstName AS Name
FROM CandSource..Contact1 C
INNER JOIN GoldMine..Contact1 G
ON C.ContactLastName = G.ContactLastName
AND C.ContactFirstName = G.ContactFirstName