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 */
Not very sure if this can be possible IN SQL.
Many Thanks Techie.
