locked
Comparing two tables RRS feed

  • Question

  • Tools: SQL server 2008

    Environment: Windows Vista

    Problem: I would like to compare table A with Table B. If table A records dont match with B then list those differences

    Giving info:

    Table A

    012345655
    012345667
    012345656
    012345673
    012345675
    012345670
    012345679
    012345674
    012345678
    005555599
    005555599
    004444499
    004444499
    003333399
    003333399
    002222299
    002222299
    001111199
    001111199
    005555599
    004444499
    003333399
    002222299
    001111199
    009555599
    009444499
    009333399
    009222299
    009111199
    099555599
    099444499
    099333399
    099222299
    099111199

    Table B

    012345655
    012345667
    012345656
    012345673
    012345675
    012345670
    012345679
    012345674
    012345678
    005555599
    003333399
    001111199
    004444499
    002222299
    009555599
    009444499
    009333399
    009222299
    009111199
    099555599
    099444499
    099333399
    099222299
    099111199

    both tables have one column and it is ID

    Table A has 34 records

    Table B has 24 records

    I am curious to know how to list/display the 10 records

    create table Dummy ( id  int )
    insert into dummy (id) values (012345655)
    insert into dummy (id) values (012345667)
    insert into dummy (id) values (012345656)
    insert into dummy (id) values (012345673)
    insert into dummy (id) values (012345675)
    insert into dummy (id) values (012345670)
    insert into dummy (id) values (012345679)
    insert into dummy (id) values (012345674)
    insert into dummy (id) values (012345678)
    insert into dummy (id) values (005555599)
    insert into dummy (id) values (005555599)
    insert into dummy (id) values (004444499)
    insert into dummy (id) values (004444499)
    insert into dummy (id) values (003333399)
    insert into dummy (id) values (003333399)
    insert into dummy (id) values (002222299)
    insert into dummy (id) values (002222299)
    insert into dummy (id) values (001111199)
    insert into dummy (id) values (001111199)
    insert into dummy (id) values (005555599)
    insert into dummy (id) values (004444499)
    insert into dummy (id) values (003333399)
    insert into dummy (id) values (002222299)
    insert into dummy (id) values (001111199)
    insert into dummy (id) values (009555599)
    insert into dummy (id) values (009444499)
    insert into dummy (id) values (009333399)
    insert into dummy (id) values (009222299)
    insert into dummy (id) values (009111199)
    insert into dummy (id) values (099555599)
    insert into dummy (id) values (099444499)
    insert into dummy (id) values (099333399)
    insert into dummy (id) values (099222299)
    insert into dummy (id) values (099111199)
    select * from Dummy -- 34 rows
    
    
    ;WITH CTE (id,rn)
    AS
    (
    SELECT id,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS rn
    FROM Dummy
    )
    
    select * from Dummy where id in (DELETE FROM CTE WHERE rn > 1)
    
    
    
    drop Table Dummy



    • Edited by Sandra VO Monday, July 30, 2012 4:36 PM
    Friday, July 27, 2012 10:26 PM

Answers

  • SELECT *

    INTO C

    FROM A
    EXCEPT
    SELECT * FROM B


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Sandra VO Monday, July 30, 2012 8:32 PM
    Monday, July 30, 2012 8:12 PM
  • ;With CTE1
    As
    (
    Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
    From	Table_A
    )
    ,CTE2
    As
    (
    Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
    From	Table_B
    )
    
    Select	ID
    From	
    		(
    			Select	ID, RN
    			From	CTE1
    				Except
    			Select	ID, RN
    			From	CTE2
    		) X


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Marked as answer by Sandra VO Monday, July 30, 2012 8:32 PM
    Saturday, July 28, 2012 7:54 AM

All replies

  • SELECT * FROM A
    EXCEPT
    SELECT * FROM B

    If you want to list rows in A that are not in B.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Murali_CHN Saturday, July 28, 2012 5:12 AM
    Friday, July 27, 2012 10:36 PM
  • Could you try this script? It will show you unique rows from each table.

    DECLARE @SQL varchar(8000) 
    DECLARE @Table1 varchar(8000) 
    DECLARE @Table2 varchar(8000) 
    SET @Table1 = 'TableA' 
    SET @Table2 = 'TableB' 
    
    DECLARE @Cols varchar(8000) 
    SET @Cols = '' 
    SELECT @Cols = @Cols + '[' + Column_Name + '],' 
      FROM INFORMATION_SCHEMA.Columns 
     WHERE Table_Name = @Table1 and DATA_TYPE <> 'xml' 
    SET @Cols=left(@cols,LEN(@cols)-1) -- Remove trailing , 
    
    SET @SQL = + 
    'SELECT Max(TableName) as TableName, ' + @Cols + 
    '  FROM ( ' + 
    '    SELECT ''' + @Table1 + ''' AS TableName, ' + @Cols + 
    '      FROM ' + @Table1 + 
    '     UNION ALL ' + 
    '    SELECT ''' + @Table2 + ''' As TableName, ' + @Cols + 
    '      FROM ' + @Table2 + 
    '    ) A ' + 
    ' GROUP BY ' + @Cols + 
    ' HAVING COUNT(*) = 1 ' + 
    ' ORDER BY ' + @Cols 
    
    -- SELECT @SQL 
    EXEC ( @SQL) 
    
    


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Saturday, July 28, 2012 1:38 AM
  • ;With CTE1
    As
    (
    Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
    From	Table_A
    )
    ,CTE2
    As
    (
    Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
    From	Table_B
    )
    
    Select	ID
    From	
    		(
    			Select	ID, RN
    			From	CTE1
    				Except
    			Select	ID, RN
    			From	CTE2
    		) X


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Marked as answer by Sandra VO Monday, July 30, 2012 8:32 PM
    Saturday, July 28, 2012 7:54 AM
  • SELECT * FROM A
    EXCEPT
    SELECT * FROM B

    If you want to list rows in A that are not in B.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    how to store the results in a table C

    A except B = C

    I am curious to know how

    Monday, July 30, 2012 3:37 PM
  • ;With CTE1
    As
    (
    Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
    From	Table_A
    )
    ,CTE2
    As
    (
    Select	ID, Row_Number() Over(Partition By ID Order by ID) As RN
    From	Table_B
    )
    
    Select	ID
    From	
    		(
    			Select	ID, RN
    			From	CTE1
    				Except
    			Select	ID, RN
    			From	CTE2
    		) X

    This is an excellent approach. I like the logic behind it. However, it did not work

    (34 row(s) affected)

    (10 row(s) affected)

    Msg 208, Level 16, State 1, Line 2
    Invalid object name 'CTE1'.

    Table A has 34 records. These records are stored in ##dummy

    In Table A or ##dummy, there are 10 duplicate records and 24 records are unique

    GO 
    
    ;WITH CTE1 
    
    AS
    
    ( SELECT  ID, row_number() OVER(PARTITION BY ID ORDER BY ID) AS RM FROM ##Dummy ) 
    
    Delete from CTE1 WHERE RM > 1 --<< remove dups
    
    GO
    
    ;with CTE2
    As
    ( Select ID, Row_Number() Over(Partition By ID Order by ID) As RN From ##Dummy )
    
    Select	ID
    From	
    		(
    			Select	ID
    			From CTE1
    				Except
    			Select	ID
    			From	CTE2
    		) X
    		
    
    
    DROP Table ##Dummy


    • Edited by Sandra VO Monday, July 30, 2012 3:56 PM
    Monday, July 30, 2012 3:55 PM
  • Hi Sandra, the CTE is not working the way you were trying to do.

    your referened CTEs can't break by Go or any other DML statement.

    YOu CTEs can only be followed by its immediate DML statements.

    Your modified query is with wrong syntax.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Monday, July 30, 2012 7:55 PM
  • Mr Steven Wang

    what do you suggest to fix the modified query. I am curious to learn. please advised

    Monday, July 30, 2012 8:11 PM
  • SELECT *

    INTO C

    FROM A
    EXCEPT
    SELECT * FROM B


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Sandra VO Monday, July 30, 2012 8:32 PM
    Monday, July 30, 2012 8:12 PM
  • There is more details available on intersect and except in MSDN link: http://msdn.microsoft.com/en-us/library/ms188055.aspx

    You may find it useful to know more about these types to reduce the number of steps in getting the result as well as performance of the queries.


    Akkiraju Ivaturi

    Tuesday, July 31, 2012 5:09 AM
  • Thanks Akkiraju!!! for sharing the info
    Tuesday, July 31, 2012 3:12 PM