Post reply

Compare tables

  • November 7, 2007 at 4:02 am

    #376743

    Hi There,

    I am using Except to compare two tables with the same structure like this,

    Select * From TableA

    Except

    Select * From TableB

    This gives me all the distinct rows from tableA not in tableB.

    But now I want to know exactly what the difference is? For example

    TableA has a row like this,

    StreetNo HouseNumberFrom HouseNumberTo EqualUnequal TimeStamp DistrictNo DistrictName

    5989 075 7 U 2005-01-02 14:18:00.000 89 Downtown West

    Which is not in TableB, but TableB has "allmost" the same record, besides a new timestamp and a new DistrictName (for ex. Downtown W.)

    How do I put together a PrimaryKey consisting of all variables (including a TimeStamp)? And can I use Except to make a Query listing the exact changes with this PrimaryKey?

    My primary goal is to find the changes between the two tables. The tables are identical tables, TableB is just one month older than TableA. If there is no changes in a row, then the row keeps the original timestamp.

    I need both small changes (new districtname) and bigger changes like completely new rows or rows not exsisting any more.

    Regards Joejoe

  • November 7, 2007 at 7:21 pm

    #749321

    Lookup "Merge" in Books Online...

    --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)

  • December 4, 2007 at 7:18 am

    #756918

    Hey Jeff,

    Do you mean Books Online 2008?

    --Ramesh

  • December 4, 2007 at 7:30 am

    #756928

    If you are using SQL 2005, one built in exe is exist for compare tables.

    Exe Name: tablediff.exe

    Location: C:\Program Files\Microsoft SQL Server\90\COM

    For more information see SQL BOL.

    🙂

  • December 6, 2007 at 11:36 am

    #757803

    AlfaAlfa's SQL Server Comparison Tool (SCT) - http://www.sql-server-tool.com - can compare data between tables.

    >How do I put together a PrimaryKey consisting of all variables (including a TimeStamp)?

    You can select between two methods of comparisons - row-by-row comparison or primary key comparison (in the second case, the program will compare data with matching PKs). You can fine-tune comparison - exclude selected columns, specify offset etc.

    Comparison "sessions" can be saved and re-played later without need of re-entering the parameters. Command line parameter can be used to fully automate comparisons.

    Dariusz Dziewialtowski.


    --
    Dariusz Dziewialtowski
    admin@database-comparison.com
    http://www.database-comparison.com

Viewing 5 posts - 1 through 4 (of 4 total)

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