SQLSERVER 2014
São 2 tabelas:
BI e Cust.
PK para BI: longitem
PK para CUST: longitem
Como posso comparar a tabela 2 no nível da coluna e encontrar a incompatibilidade, se houver, com base na chave primária (PK) que é longitem.
Dados e DDL se parecem com:
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'`
eu tentei algo como
Select * from BI as B inner join cust as c on b.longitem = c.longitem
where a.chain <> b.chain
or a.sku <> b.sku_id
or a.color_id <> b.color_id
or a.color_desc <> b.color_desc
or a.size_id <> b.size_id
or a.size_name <> b.size_name
Saída Esperada
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
Agradeço muito sua ajuda