Post reply

How To Compare Duplicate rows

  • April 25, 2009 at 8:56 am

    #130285

    Hi All,

    I have two different tables with duplicate rows and non-duplicate rows. I have to analyze the data and make sure the date is correct b/w two DB. I just what to compare and I don't want to use cursor because it takes time.

    Is there any better way to compare and I don't want to delete the duplicates?

    Guys please help I appreciate your help.

    Thanks in advance.

  • April 25, 2009 at 9:08 am

    #983965

    Hi

    You can use a partitioned row_number over the field(s) which define the "uniqueness":

    DECLARE @t TABLE (NonUniqueId INT)

    INSERT INTO @t

    SELECT 1

    UNION ALL SELECT 2

    UNION ALL SELECT 2

    UNION ALL SELECT 1

    UNION ALL SELECT 3

    ; WITH todo (RowNumber) AS

    (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY NonUniqueId ORDER BY NonUniqueId)

    FROM @t

    )

    DELETE FROM todo WHERE RowNumber != 1

    SELECT * FROM @t

    Greets

    Flo

  • April 25, 2009 at 9:13 am

    #983967

    Thanks a lot for a very quick reply. If I have any questions I will let you know.

    Thanks again

  • April 25, 2009 at 9:28 am

    #983971

    I'm glad that I could help!

    Best wishes

    Flo

  • April 25, 2009 at 11:20 am

    #983991

    The EXCEPT and INTERSECT operators can also help you compare data between two different queries. They provide a pretty easy way to compare data.

    http://msdn.microsoft.com/en-us/library/ms188055.aspx

  • April 25, 2009 at 10:14 pm

    #984028

    I tried except and intersect. I have a very large data base the query runs very slow. Actully I will put my question in a diffrent way.

    I have a table1 ........

    Name Address

    x xyz

    y 123

    z abc

    I have an another table2

    Name1 Address2

    x xyz

    a 456

    b hhh

    and I want a result some thing ......... like this

    Name Address Name1 Address1

    x xyz x xyz

    y 123 Blank Blank

    z abc Blank Blank

    Blank Blank a 456

    Blank Blank b hhh

    So that I can show what exactly is missing.............

    Thanks a lot in advance.

  • April 25, 2009 at 10:33 pm

    #984029

    Based off your example you could run a query like this. However, this would pull back all the data from both tables and as you said the data is large and this could take quite a while. You should at least add the where clause that is commented out in the example to only limit the rows with no matches in both tables.

    Declare @table1 Table (Name varchar(10), Address varchar(10))

    Declare @table2 Table (Name2 varchar(10), Address2 varchar(10))

    Insert Into @table1

    Select 'x', 'xyz' UNION ALL

    Select 'y', '123' UNION ALL

    Select 'z', 'abc'

    Insert Into @table2

    Select 'x', 'xyz' UNION ALL

    Select 'a', '456' UNION ALL

    Select 'b', 'hhh'

    Select *

    From @table1 A FULL OUTER JOIN

    @table2 B ON

    A.Address = B.Address2 and

    A.Name = B.Name2

    --Where A.Name IS NULL OR B.Name2 IS NULL

  • April 26, 2009 at 2:41 pm

    #984124

    Thanks a lot

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

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