Compare data in different tables with same structure

Your rating: None Average: 2 (3 votes)

Here is the SQL to compare the differences in the data in different tables with same structure. Will be helpful for comparing the data between databases. This works in MS SQL Server if both databases are on same server or if there is a linked server configured for the other database.

SELECT  * FROM
(SELECT Max(TableName) AS TableName, FIELD1, FIELD2, FIELD3
FROM (SELECT 'HRDB1..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB1..PS_MY_RECORD
UNION ALL SELECT 'HRDB2..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB2..PS_MY_RECORD ) A  
GROUP BY FIELD1, FIELD2, FIELD3
HAVING COUNT(*) = 1) A
ORDER BY 1

Replace table names, database names and fields list accordingly.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
CompShack's picture
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Different way as an Oracle SQL

Hey Ashar,

Your post reminded me of an SQL that I wrote a while back with pretty much the same requirement. I have done it differently and it works on Oracle database. So, here it is :)

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ashar's picture
User offline. Last seen 3 years 17 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Nice to hear that.. I had to

Nice to hear that.. I had to use this to verify that the newly added config data for a custom module are migrated properly..

Thanks,

Ashar