How to compare data from two different databases?
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
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
to get info about PK:
use yourDB
go
SELECT
TC.TABLE_NAME, CU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_C
INNER JOIN
INFORMATION_SCHEMA.KEY_COL
ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
use yourDB
go
SELECT
TC.TABLE_NAME, CU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_C
INNER JOIN
INFORMATION_SCHEMA.KEY_COL
ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Knight,
I get the error "Cannot resolve collation conflicy for equal to operation" with your script.
I get the error "Cannot resolve collation conflicy for equal to operation" with your script.
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) ?
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) ?
ASKER
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/questions/21048175/Cannot-resolve-collation-conflict-for-equal-to-operation.html
which helped.
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/questions/21048175/Cannot-resolve-collation-conflict-for-equal-to-operation.html
which helped.
FROM DB_A..tblCompany A
JOIN DB_B..arcust B
ON A.CompanyID = B.custno