Row-by-Row comparison

  • I have two tables, stg,main with the same structure.Each day data is refeshed into stg from the flat file. And from there into main.Before the main table is loaded,I need to validate if the data in all the columns of the row is matching.The main challenge is I have 100 columns in these tables. Can any one recommend me of a optimal solution to compare data across the tables ?The bottom line is I should not create duplicate rows into the main table.

    Thanks in Advance.

  • Suggest you look at EXCEPT (and maybe INTERSECT) in BOL

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • search google on TableDiff utility, it comes with SQL 2005 installation used for table comparisons just for the kind of task u r looking for.

  • Sincere Thanks for coming up with your suggestions.At this point I have tried out Except operator and this seems to be working reasonably well for me.

  • rvkhadava (6/5/2009)


    I have two tables, stg,main with the same structure.Each day data is refeshed into stg from the flat file. And from there into main.Before the main table is loaded,I need to validate if the data in all the columns of the row is matching.The main challenge is I have 100 columns in these tables. Can any one recommend me of a optimal solution to compare data across the tables ?The bottom line is I should not create duplicate rows into the main table.

    Thanks in Advance.

    Hi,

    I have implemented the same task using SSIS and it works like a charm. I have used lookup table task in SSIS.

    http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx ===> use method 2, it works for me.

    Hope this helps,

    \\K

    [http://sqlquest.blogspot.com/]

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Hi,

    Looking for a method of comparing rows in the same table, I ran across this thread. I thought I would add my 2p to the debate:

    [Code="sql"]

    SET @result = 0

    IF EXISTS (SELECT EX.DocumentID FROM

    (SELECT DocumentID, DocTitle, SortLetter, SortNumber FROM

    PMProcessProcedures WHERE PMProcessID = @ID

    EXCEPT

    SELECT DocumentID, DocTitle, SortLetter, SortNumber FROM

    PMProcessProcedures WHERE PMProcessID = @Approved) EX)

    SET @result = 1[/code]

    This will set @result if the two rows are different.

    Hope this helps someone...

  • Here is your Solution

    http://www.sqlservercentral.com/scripts/Table+Data+Comparison/141216/

    get the SQL Statement after running procedure and then change EXISTS with NOT EXISTS and you all done.

    Sheraz Mirza::hehe:

  • Just an FYI, this is 6 year old thread.

Viewing 8 posts - 1 through 7 (of 7 total)

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