How to Compare the values in two different tables and return if any of value from a row is different in SQL Server

Question

I have two tables [Product1] and [Product2_transform]

create table [product1](
     [product_id]           nvarchar(256)
    ,[product_rev]          nvarchar(256)
    ,[product_name]         nvarchar(256)
    ,[product_description]  nvarchar(256)
    ,[product_owner]        nvarchar(256)
    ,[product_group]        nvarchar(256)
    ,[product_type]         nvarchar(256)
);

create table [product2_transform](
     [product_id]           nvarchar(256)
    ,[product_rev]          nvarchar(256)
    ,[product_name]         nvarchar(256)
    ,[product_description]  nvarchar(256)
    ,[product_owner]        nvarchar(256)
    ,[product_group]        nvarchar(256)
    ,[product_type]         nvarchar(256)
);


insert into [product1] values
     ('111' ,'AAA',  'CAR'          ,   'SPARE PARTS'       ,   'Nissan'        ,   'Nissan'    ,   'AUTOMOTIVE'    )   
    ,('222' ,'BBB',  'MODEL'        ,   'DESGIN'            ,   'NIFT'          ,   'NIFT'      ,   'FASHION'       )
    ,('333' ,'CCC',  'REAR'         ,   'REAR MATERIAL'     ,   'KLM'           ,   'KLM GROUP' ,   'MANUFACTURING' )
    ,('444' ,'DDD',  'FINACLE 2.0'  ,   'BANKING PRODUCT'   ,   'IFLEX'         ,   'ORACLE'    ,   'SOFTWARE'      )
    ,('555' ,'EEE',  'TYRE'         ,   'CEAR TYRES'        ,   'TATA MOTORS'   ,   'TATA'      ,   'AUTOMOTIVE'    );

insert into [product2_transform] values
     ('111',    'AAA', 'CAR'            ,   'SPARE PARTS Ford'  ,   'Ford'          , 'Nissan  '    , 'AUTOMOTIVE LTD')
    ,('222',    'BBB', 'MODEL'          ,   'DESGIN'            ,   'NIFTY'         , 'NIFT'        , 'FASHION'       )
    ,('333',    'CCC', 'REAR Head Left' ,   'REAR MATERIAL'     ,   'KLM'           , 'KLM GROUP'   , 'MANUFACTURING' )
    ,('444',    'DDD', 'FINACLE 2.5'    ,   'BANKING PRODUCT'   ,   'Oracle IFLEX'  , 'ORACLE'      , 'SOFTWARE'      )
    ,('555',    'EEE', 'SEAT TYRE'      ,   'CEAR TYRES'        ,   'TATA BANCS'    , 'TATA'        , 'AUTOMOTIVE'    );

I want the difference values in [product1] and [product2] and place it in [Product_post_validation] table..

create table [product_post_validation](
     [product_id]           nvarchar(256)
    ,[product_rev]          nvarchar(256)
    ,[validation_column]    nvarchar(256)
    ,[value_in_transform]   nvarchar(256)    
    ,[value_in_output]      nvarchar(256)
);

Expected result in [product_post_validation] is: Product_id / Product_rev / validation_column / value_in_transform / value_in_output

111 AAA Product_description SPARE PARTS SPARE PARTS Ford
111 AAA Product_owner   Nissan  Ford
111 AAA Product_TYPE    AUTOMOTIVE  AUTOMOTIVE LTD
333 CCC Product_Name    REAR    REAR Head Left
444 DDD Product_Name    FINACLE 2.0 FINACLE 2.5
444 DDD Product_owner   IFLEX   Oracle IFLEX 
555 EEE Product_Name    TYRE    SEAT TYRE
555 EEE Product_owner   TATA MOTORS TATA BANCS

please help in writing query...

Actually i wrote in oracle query its working fine, now i want to write in MS sql server,so please assist.

with comp as (  
  select /*+ qb_name(CDC_GROUP) */  
      "PRODUCT_ID", "PRODUCT_REV", "PRODUCT_NAME", "PRODUCT_DESCRIPTION",  
      "PRODUCT_OWNER", "PRODUCT_GROUP", "PRODUCT_TYPE",  
    case when count(*) over(partition by "PRODUCT_ID", "PRODUCT_REV") - Z##NEW_CNT <= 1  
      then 'PRODUCT2_TRANSFORM'  
      else 'PRODUCT1'  
    end TBL  
  FROM (  
    select /*+ qb_name(COMPARE) NO_MERGE */  
      "PRODUCT_ID", "PRODUCT_REV", "PRODUCT_NAME", "PRODUCT_DESCRIPTION",  
      "PRODUCT_OWNER", "PRODUCT_GROUP", "PRODUCT_TYPE",  
      sum(Z##NEW_CNT) Z##NEW_CNT  
    FROM (  
      select /*+ qb_name(old) */  
      "PRODUCT_ID", "PRODUCT_REV", "PRODUCT_NAME", "PRODUCT_DESCRIPTION",  
      "PRODUCT_OWNER", "PRODUCT_GROUP", "PRODUCT_TYPE",  
      -1 Z##NEW_CNT  
      from PRODUCT1 O  
      union all  
      select /*+ qb_name(new) */  
      "PRODUCT_ID", "PRODUCT_REV", "PRODUCT_NAME", "PRODUCT_DESCRIPTION",  
      "PRODUCT_OWNER", "PRODUCT_GROUP", "PRODUCT_TYPE",  
      1 Z##NEW_CNT  
      from product2_transform N  
    )  
    group by  
      "PRODUCT_ID", "PRODUCT_REV", "PRODUCT_NAME", "PRODUCT_DESCRIPTION",  
      "PRODUCT_OWNER", "PRODUCT_GROUP", "PRODUCT_TYPE"  
    having sum(Z##NEW_CNT) != 0  
  )  
)  
select * from comp  
unpivot(val for col in(PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_OWNER, PRODUCT_GROUP, PRODUCT_TYPE))  
pivot(max(val) for tbl in ('PRODUCT1' PRODUCT1, 'PRODUCT2_TRANSFORM' PRODUCT2_TRANSFORM))  
where decode(PRODUCT1, PRODUCT2_TRANSFORM, 0, 1) = 1  
order by 1,2,3; 

Answer 1

First of all: thank you for interesting challenge :)

Potential solution is:

;with [data] as (
    select
         [product_id]           =   isnull([p1].[product_id],   [p2].[product_id])
        ,[product_rev]          =   isnull([p1].[product_rev],  [p2].[product_rev])
        ,[validation_column1]   =   [p1_c].[value]('(local-name(.))[1]',    'nvarchar(256)')
        ,[validation_column2]   =   [p2_c].[value]('(local-name(.))[1]',    'nvarchar(256)')
        ,[validation_value1]    =   iif([p1_c].[value]('(@xsi:nil)[1]', 'bit') = 'true', null, [p1_c].[value]('(.)[1]', 'nvarchar(256)'))
        ,[validation_value2]    =   iif([p2_c].[value]('(@xsi:nil)[1]', 'bit') = 'true', null, [p2_c].[value]('(.)[1]', 'nvarchar(256)'))
    from     
        [product1]              as  [p1]
    full join
        [product2_transform]    as  [p2]
    on
            [p1].[product_id]   =   [p2].[product_id]
        and [p1].[product_rev]  =   [p2].[product_rev]
    cross apply
        (
            select [data] = convert(xml, (select [p1].* for xml path(''), elements xsinil))                      
        )                       as  [p1x]
    cross apply
        (
            select [data] = convert(xml, (select [p2].* for xml path(''), elements xsinil))                      
        )                       as  [p2x]
    cross apply 
        [p1x].[data].[nodes]('*')   as  [p1_t]([p1_c])
    cross apply 
        [p2x].[data].[nodes]('*')   as  [p2_t]([p2_c])
)
insert into [product_post_validation](
     [product_id]           
    ,[product_rev]          
    ,[validation_column]    
    ,[value_in_transform]       
    ,[value_in_output]  
)
select
     [product_id]           =   [product_id]            
    ,[product_rev]          =   [product_rev]           
    ,[validation_column]    =   isnull([validation_column1], [validation_column2])     
    ,[value_in_transform]   =   [validation_value1]   
    ,[value_in_output]      =   [validation_value2]     
from 
    [data]
where       
        isnull(nullif([validation_column1], [validation_column2]),  nullif([validation_column2],    [validation_column1]))  is null
    and isnull(nullif([validation_value1],  [validation_value2]),   nullif([validation_value2],     [validation_value1]))   is not null
order by 
     [product_id]   asc
    ,[product_rev]  asc; 

Full testing script: https://pastebin.com/0eBuiHMX

Answer 2

I would do this by unpivoting the data and then comparing:

with p1 as (
      select p.product_id, p.product_rev, v.col, v.val
      from product1 p cross apply
           (values ('product_name', product_name),
                   ('product_description', product_description),
                   ('product_owner', product_owner),
                   ('product_group', product_group),
                   ('product_type', product_type)
           ) v(col, val)
     ),
     p2 as (
      select p.product_id, p.product_rev, v.col, v.val
      from Product2_Transform p cross apply
           (values ('product_name', product_name),
                   ('product_description', product_description),
                   ('product_owner', product_owner),
                   ('product_group', product_group),
                   ('product_type', product_type)
           ) v(col, val)
     )
select p1.product_id, p2.product_id, p1.which as validation_column,
       p2.val as val_in_transform, p1.val as val_in_product
from p1 join
     p2
     on p1.product_id = p2.product_id and
        p1.product_rev = p2.product_rev and
        p1.col = p2.col and
        (p1.val <> p2.val or p1.val is null and p2.val is not null or p1.val is not null and p2.val is null);

The big issue with the unpivot is that the types need to be compatible. They all appear to be strings, so this should be fine.

Note that this does handle NULL values. It does not handle missing product/rev pairs between the two tables. That is easily handled by using full join and then coalesce() in the select. Your question is not clear if this is a priority.