none
FULL Examine two table with all columns data RRS feed

  • Question

  • Hello, I have two tables with identical columns.
    And I want to Examine these two tables and find all unmatched rows from both table. I also wants to include 'Comments' column at the end to include comment.

    create table A1 (pol char(10), col char(10), effdate datetime, pisnull char(1), cisnull char(1), trans char(5), suppl char(5), insurer integer, city char(10), postal char(7),
    address char(40), ccode integer, email char(40), cmap char(10))

    Insert into A1 values ('120','28','2019-01-01','Y','Y','A10','PP',4, 'TO','T1X 1M1','865 Manchester',7700,'ssmith@gmail.com','UU')
    Insert into A1 values ('130','88','2019-07-01','N','Y','A11','WW',4, 'ER','K1t 2H6','869 Manchester',7700,'plain@yahoo.com','UU')
    Insert into A1 values ('160','28','2019-04-01','Y','B','A10','PP',7, 'TO','B2X 1M2','864 Manchester',7700,'jkh@gmail.com','UU')
    Insert into A1 values ('122','45','2019-06-01','T','Y','A10','RR',4, 'RR','K2B 4H3','862 Manchester',7900,'thst@hotmail.com','UU')
    Insert into A1 values ('128','88','2018-01-01','Y','Y','A12','PP',4, 'TO','L7H 7J8','861 king',7900,'ddtrs@gmail.com','AA')

    create table B1 (pol char(10), col char(10), effdate datetime, pisnull char(1), cisnull char(1), trans char(5), suppl char(5), insurer integer, city char(10), postal char(7),
    address char(40), ccode integer, email char(40), cmap char(10))

    Insert into B1 values ('120','28','2019-01-01','Y','Y','A10','PP',4, 'TO','T1X 1M1','865 Manchester',7700,'ssmith@gmail.com','UU')
    Insert into B1 values ('130','88','2019-07-01','N','Y','A11','WW',4, 'ER','K1t 2H6','869 Manchester',7700,'plain@yahoo.com','UU')
    Insert into B1 values ('160','28','2019-04-01','Y','B','A10','PP',7, 'TO','B2X 1M2','864 Manchester',99999,'jkh@gmail.com','UU')
    Insert into B1 values ('122','45','2019-06-01','T','Y','A10','RR',4, 'RR','K2B 4H3','862 Dixie',7900,'thst@hotmail.com','UU')
    Insert into B1 values ('129','88','2018-01-01','Y','Y','A12','PP',4, 'TO','L7H 7J8','861 Manchester',7900,'ddtrs@gmail.com','AA')

    --Expected result after full examine

    pol col effdate   pisnull cisnull trans suppl insurer city postal address ccode email cmap   COMMENTS
    128 88  2018-01-01  Y Y A12   PP    4 TO  L7H 7J8 861 king 7900 ddtrs@gmail.com AA       Missing in table B1
    160 28  2019-04-01  Y B A10   PP    7 TO  l2X 1M2 864 Dixie 7700 jkh@gmail.com   UU      Not matching all col data with B1
    160 28  2019-04-01  Y B A10   PP    7 TO  l2X 1M2 864 Dixie 99999 jkh@gmail.com   UU    Not matching all col data with A1
    129 88  2018-01-01  Y Y A12   PP    4 TO  L7H 7J8 861 king 7900 ddtrs@gmail.com AA       Missing in table A1






    • Edited by Kevin Ruth Saturday, August 17, 2019 7:35 PM
    Saturday, August 17, 2019 5:50 AM

All replies

  • I'm assuming that your primary key is pol,

    Declare @Compare table(pol char(10), col char(10), effdate datetime, pisnull char(1), cisnull char(1), trans char(5), suppl char(5), insurer integer, city char(10), postal char(7),
    address char(40), ccode integer, email char(40), cmap char(10), COMMENTS varchar(25))
    
    Insert @Compare(pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, COMMENTS)
    Select pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, 'Missing in table B1' As COMMENTS
    From A1
    Except
    Select pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, 'Missing in table B1' As COMMENTS
    From B1;
    Insert @Compare(pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, COMMENTS)
    Select pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, 'Missing in table A1' As COMMENTS
    From B1
    Except
    Select pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, 'Missing in table A1' As COMMENTS
    From A1;
    
    With cte As
    (Select pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, COMMENTS,
      Count(*) Over(Partition By pol) As Cnt, Row_Number() Over(Partition By pol Order By COMMENTS Desc) As rn
    From @Compare)
    Select pol, col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap, 
      Case When Cnt = 1 Then COMMENTS
        When rn = 1 Then 'Not matching all col data with B1'
        When rn = 2 Then 'Not matching all col data with A1' End As COMMENTS
    From cte
    Order By pol, rn Desc;
    
    Note that given your sample data, in addition to the expected results you gave this also returns that pol=122 does not match because the address column does not match for the two rows.

    Tom

    Saturday, August 17, 2019 6:43 AM
  • Thank you so much Tom
    Saturday, August 17, 2019 7:37 PM
  • Hi Kevin,

    You could also try this one: 

    Declare @comment table(pol int, ccode int,COMMENTS varchar(50))
    Insert @comment values (128,7900,'Missing in table B1') ,(160,7700,'Not matching all col data with B1'),
    (160,99999,'Not matching all col data with A1'),(129,7900,'Missing in table A1')
    
    
    select a.*,b.comments from 
    (select * from a1
    union 
    select * from b1)a 
    join @comment b
    on a.pol= b.pol and a.ccode=b.ccode 

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 7:07 AM
  • Hi Kevin

    -- no Key to compare? (must compare complete row)
    select 
      case when CHK_A1.CHKSUM is null and CHK_B1.CHKSUM is not null then 'Missing in A1'
           when CHK_B1.CHKSUM is null and CHK_A1.CHKSUM is not null then 'Missing in B1'
           else 'Equal'
      end as Comment
    , CHK_A1.*
    , CHK_B1.*
    from      (select checksum(*) CHKSUM, * from A1) CHK_A1
    full join (select checksum(*) CHKSUM, * from B1) CHK_B1
    on CHK_A1.CHKSUM=CHK_B1.CHKSUM
    
    -- assuming pol is Key
    select
      case when CHK_A1.pol is null then 'Missing on A1'
           when CHK_B1.pol is null then 'Missing on B1'
           when CHK_A1.CHKSUM<>CHK_B1.CHKSUM then 'Different'
           else 'Equal'
      end as Comment
    , CHK_A1.*
    , CHK_B1.*
    from      (select checksum(col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap) CHKSUM, * from A1) CHK_A1
    full join (select checksum(col, effdate, pisnull, cisnull, trans, suppl, insurer, city, postal, address, ccode, email, cmap) CHKSUM, * from B1) CHK_B1
    on CHK_A1.pol=CHK_B1.pol
    

    You may fine tune the comments.

    Blaise.

    Wednesday, August 21, 2019 9:16 AM
  • Hi Kevin

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 26, 2019 7:38 AM