We help IT Professionals succeed at work.

How to compare data from two different databases?

222 Views
Last Modified: 2010-03-20
I have two differnet databases that have different field names, and different datatypes. I need to compare the data between these databases to find out which PK's exist or do not exist. My task is to synchronize the PK's in these tables. I have programmed a tool for the DB updates. Now, I need to find which ID's need the updates.
For example, Database A fieldname "CompanyID" (varchar 50) = Database B fieldname "custno" (char 6)
SELECT *
FROM DB A.tblCompany INNER JOIN DB B.arcust ON
A.CompanyID = B.custno
Comment
Watch Question

CERTIFIED EXPERT

Commented:
SELECT *
FROM DB_A..tblCompany A
JOIN DB_B..arcust B
ON A.CompanyID = B.custno
EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
to get info about PK:


use yourDB
go
  SELECT
            TC.TABLE_NAME, CU.COLUMN_NAME
        FROM
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
            INNER JOIN
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
            ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
            WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Knight,
I get the error "Cannot resolve collation conflicy for equal to operation" with your script.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
OK, you can add the "COLLATE" keyword with a compatible collation that suits both databases - aint going to work though if it needs to transform to an incompatible collation - such as an unsupported code set, different character sets, or unicode vs non-unicode data... However, overlooking that for the moment...

SELECT *
FROM DB A.tblCompany INNER JOIN DB B.arcust ON
A.CompanyID = B.custno COLLATE Latin1_General_CI_AS

Now that applies only to char, nchar, varchar, nvarchar (and text / ntext but surely not as a key). So you have to check the type of columns as well and convert them into compatable / comparable data types...

There is also a small challenge if using linked servers - especially if that linked server supports different code pages...

Can you describe a bit more about your code pages ? is there any portugese in there by chance (seem to remember a question a little way back) ?

Author

Commented:
Mark, thanks for the COLLATE tip. below is the working code for the compare.
Basically I just needed to know if the PK existed in the other table.
SELECT     CompanyID, CompanyName
FROM         tblCompany
WHERE     (CompanyID COLLATE database_default NOT IN
                          (SELECT     custno COLLATE database_default
                            FROM          National.dbo.arcust))
ORDER BY CompanyID

Found this on the exchange as well
https://www.experts-exchange.com/Databases/Q_21048175.html
which helped.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.