How to compare data of two table based on key column and get custom report in sql server

Question

There are 2 table:

BI and Cust.

PK for BI : longitem

PK for CUST: longitem

How can I compare the 2 table on column level and find the mismatch if any exists based on Primary Key (PK) that is longitem.

Data and DDL looks like:

CREATE  TABLE BI
(
chain VARCHAR(500),
sku VARCHAR (150),
longitem VARCHAR(500),
color_id              INT,
color_desc         VARCHAR(200),
size_id  INT,
size_name          VARCHAR(200)
 
)
INSERT BI
 
 
 
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30407092',              '33300000200010354003',           035,       'WOOL GABARDINE', 243,              '40  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '29308392',              '33300000200040204602',           020,       'TONAL WEAVE',                256,       '46  REG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '29310505',              '33300000200160033601',           003,       'MED DOT STRP',   231,   '36  SHR'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30743884',              '33300000200180044403',           004,       'SMALL TWILL 2B', 253,  '44  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '28994986',              '33300000200340413803',           041,       'PLAIN PINDOT 3B',238,  '38  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30745574',              '33300000200780413801',           041,       'STRUCTURED BTN', 236,              '38  SHR'            UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30742241',              '33300000200790414200',           041,       'BASIC BL BT NTC',248,   '42  REG'             UNION ALL
SELECT 'ECOMM',                                '44746212',   '33300010002560010179',              001,       'WEST-US-0602021',419,              'OS'
 
 
 
CREATE  TABLE CUST
(
chain_ID VARCHAR(500),
sku_id VARCHAR (150),
longitem VARCHAR(500),
color_id              INT,
color_desc         VARCHAR(200),
size_id  INT,
size_name          VARCHAR(200)
 
)
INSERT CUST
 
 
 
SELECT 'FSC',      '30407092',        '33300000200010354003',           035,              'WOOL GABARDINE', 243,            '40  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '29308392',              '33300000200040204602',           020,       'TONAL WEAVE',                256,       '46  REG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '293XXX05',              '33300000200160033601',           003,       'DOT STRP',   231,            '36  SHR'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30743884',              '33300000200180044403',           004,       'SMALL TWILL 2B', 253,  '44  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '28994986',              '33300000200340413803',           041,       'PLAIN PINDOT 3B',238,  '38  LNG'             UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30745574',              '33300000200780413801',           041,       'STRUCTURED BTN', 236,              '38  SHR'            UNION ALL
SELECT 'FSC - FACTORY STORE CONCEPTS',           '30742241',              '33300000200790414200',           111,       'BASIC BL BT NTC',248,   '42  REG'             UNION ALL
SELECT 'ECOMM',                                '44746212',   '33300010002560010179',              001,       'WEST-US-0602021',419,              'OS'
 

Expected Output:

/*

longitem            |Source_Column_name  | Target_column_name|SRC_VALUE                   |TARGET_VALUE
33300000200010354003|chain               |chain_ID           |FSC - FACTORY STORE CONCEPTS| FSC
33300000200160033601|SKU                 |SKU_ID             |29308392                    |293XXX05
33300000200790414200|color_id            |color_id           |041                         |111
*/ 

Thank You so much techie.																			

Answer 1

There are different scenarios to do your job

You can find some of them here:

https://www.geeksforgeeks.org/how-to-compare-columns-in-two-different-tables-in-sql/

Answer 2

Hi @Rosalina5

Please check this query:

;WITH CTE AS
(
SELECT B.longitem,B.chain,B.sku,B.color_id,B.color_desc,B.size_id,B.size_name,
       C.chain_ID,C.sku_id,C.color_id AS Cust_color_id,C.color_desc AS Cust_color_desc,C.size_id AS Cust_size_id,C.size_name AS Cust_size_name
FROM BI B JOIN CUST C ON B.longitem=C.longitem
WHERE B.chain <> C.chain_ID 
   OR B.sku <> C.sku_id
   OR B.color_id <> C.color_id
   OR B.color_desc <> C.color_desc
   OR B.size_id <> C.size_id
   OR B.size_name <> C.size_name
)
SELECT longitem,A.* 
FROM CTE
CROSS APPLY(VALUES('chain','chain_ID',chain,chain_ID)
                 ,('sku','sku_id',sku,sku_id)
				 ,('color_id','color_id',CAST(color_id AS varchar(20)),CAST(Cust_color_id AS varchar(20)))
				 ,('color_desc','color_desc',color_desc,Cust_color_desc)
				 ,('size_id','size_id',CAST(size_id AS varchar(20)),CAST(Cust_size_id AS varchar(20)))
				 ,('size_name','size_name',size_name,Cust_size_name))
		  A(Source_Column_name,Target_column_name,SRC_VALUE,TARGET_VALUE)
WHERE SRC_VALUE <> TARGET_VALUE

Output:

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.