Comparison for 75 tables in two different SQL Server databases

Question

I'm trying to do a column by column, row by row comparison for 75 tables in two different databases. The tables contain up to 165 million records.

I need to determine if there are any missing records, and we need to identify any columns that contain different values. Potential problems include decimal errors.

Thanks for any assistance or advice.

Answer 1

Duplicate thread with SQL Server Central: https://www.sqlservercentral.com/forums/topic/comparison-for-75-tables-in-two-different-sql-server-databases#post-3966337

For convenience, I'm copying the answer I posted on SSC:

I believe there is a Data Compare option in Visual Studio if you have SSDT installed.

Else you can run queries like this:

SELECT *
FROM   dbA.dbo.tbl A
FULL JOIN dbB.dbo.tbl B ON A.keycol = B.keycol
WHERE NOT EXISTS (SELECT A.* INTERSECT SELECT B.*)

This assumes that you want to compare each and every column. If you want to exempt one or more columns you need replace A.* and B.* with explicit column lists.

I suggest that you try the pattern above on one table before you proceed further.

Answer 2

Hi dangtran-6885,
Agree with others.
You can go to SSDT Tools menu ->SQL Server-> new data comparison to compare records between multiple tables.

After selecting connection to Source Database and Target Database in the Choose Source and Target Databases dialog box, you can select tables to compare.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

See:
https://learn.microsoft.com/en-us/sql/ssdt/how-to-compare-and-synchronize-the-data-of-two-databases?view=sql-server-ver15