Post reply

Comparing 2 table values

  • June 8, 2015 at 11:30 am

    #308908

    Hi all,

    I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.Any suggestions?

    Thank You

  • June 8, 2015 at 11:39 am

    #1803758

    Take a look at the CHECKSUM() and CHECKSUM_AGG() functions.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 8, 2015 at 1:42 pm

    #1803799

    CHECKSUM_AGG compares one column at the time.

  • June 8, 2015 at 1:44 pm

    #1803800

    Barcelona10 (6/8/2015)

    CHECKSUM_AGG compares one column at the time.

    If you want to calculate a checksum across multiple columns and rows, then you can can wrap checksum_agg() around checksum() like this:

    checksum_agg( checksum(col1,col2,col3) )

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 8, 2015 at 1:55 pm

    #1803805

    result is int.it is not telling me which column is changed.

  • June 8, 2015 at 1:56 pm

    #1803806

    I think i should go with EXCEPT

  • June 8, 2015 at 1:57 pm

    #1803807

    Or RED GATE's Data Compare

  • June 9, 2015 at 5:13 am

    #1803898

    yes , except is good option.

    Raj Acharya

  • June 9, 2015 at 5:22 am

    #1803901

    Barcelona10 (6/8/2015)

    ...Col1 will have value that application will generate(id)....

    SQL Server has the IDENTITY attribute and Sequences for this - why reinvent the wheel?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 9, 2015 at 7:55 am

    #1803963

    Barcelona10 (6/8/2015)

    I think i should go with EXCEPT

    If you want to return a result of rows from one set that are not included in another set, then EXCEPT is the way to go.

    The following is more for a scenario where you need to derive a single checksum vaue for a rowset and then save it in an audit table for future reference, like verifying that rowsets are not being updated over time.

    checksum_agg( checksum(col1,col2,col3) )

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • June 9, 2015 at 10:28 am

    #1804029

    ...Col1 will have value that application will generate(id)....

    It is not some random number.There is some logic involved .

    thank you all for your replies

Viewing 11 posts - 1 through 10 (of 10 total)

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