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.  
NetzillaKBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

soco180Commented:
First you are going to have to define what a duplicate is...In the below query I assumed a duplicate means a person with the same last name and same first name. That is very loose logic. You might want to include city, state and zip.

How do you define a duplicate?

SELECT *
FROM CandSource..Contact1 C
INNER JOIN GoldMine..Contact1 G
    ON C.ContactLastName = G.ContactLastName
    AND C.ContactFirstName = G.ContactFirstName
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NetzillaKBAuthor Commented:
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
0
NetzillaKBAuthor Commented:
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?
0
soco180Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Contact Management

From novice to tech pro — start learning today.