Post reply

Comparison for 75 tables in two different SQL Server databases

  • December 22, 2021 at 2:21 pm

    #3966336

    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.

  • December 22, 2021 at 2:26 pm

    #3966337

    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.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • December 22, 2021 at 8:43 pm

    #3966476

    This whole thing is a bit concerning.  Why do you have two databases on the same instance and why have they become different?  There are a couple of slick tricks to mitigate all this depending on what the answers to those two questions are.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply