Post reply

Row-by-Row comparison

  • June 5, 2009 at 4:18 am

    #210668

    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.

  • June 5, 2009 at 4:39 am

    #1005129

    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

  • June 5, 2009 at 8:44 am

    #1005335

    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.

  • June 16, 2009 at 5:25 am

    #1010098

    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.

  • June 17, 2009 at 8:39 am

    #1010994

    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 🙂

  • July 8, 2010 at 6:29 am

    #1190192

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

  • May 20, 2016 at 7:21 am

    #1880480

    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:

  • May 20, 2016 at 8:21 am

    #1880505

    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