Início / coding / Perguntas / 76931545
Accepted
Angel D'souza
Asked: 2023-08-19 01:43:21 +0800 CST

Compare os dados de duas tabelas com base na coluna-chave e obtenha um relatório personalizado

  • 772

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

sql-server

1 respostas

  1. Best Answer

    Tente a seguinte solução genérica.

    Ele está comparando duas tabelas com base nos mesmos nomes de coluna.

    Assim, você pode criar uma visão (ou outro CTE) para a 2ª tabela apenas para nomear as colunas exatamente como a 1ª tabela.

    SQL

    -- DDL and sample data population, start
DECLARE @BI  TABLE 
(
    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
VALUES
('FSC - FACTORY STORE CONCEPTS', '30407092', '33300000200010354003', 035, 'WOOL GABARDINE', 243, '40  LNG'),
('FSC - FACTORY STORE CONCEPTS', '29308392', '33300000200040204602', 020, 'TONAL WEAVE',    256, '46  REG'),
('FSC - FACTORY STORE CONCEPTS', '29310505', '33300000200160033601', 003, 'MED DOT STRP',   231, '36  SHR'),
('FSC - FACTORY STORE CONCEPTS', '30743884', '33300000200180044403', 004, 'SMALL TWILL 2B', 253, '44  LNG'),
('FSC - FACTORY STORE CONCEPTS', '28994986', '33300000200340413803', 041, 'PLAIN PINDOT 3B',238, '38  LNG'),
('FSC - FACTORY STORE CONCEPTS', '30745574', '33300000200780413801', 041, 'STRUCTURED BTN', 236, '38  SHR'),
('FSC - FACTORY STORE CONCEPTS', '30742241', '33300000200790414200', 041, 'BASIC BL BT NTC',248, '42  REG'),
('ECOMM',                        '44746212', '33300010002560010179', 001, 'WEST-US-0602021',419, 'OS');

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

;WITH rs AS
(
    SELECT Src = 1
        , longitem 
        , c.value('local-name(.)','SYSNAME') AS [Key]
        , c.value('text()[1]','VARCHAR(MAX)') AS [Value]
    FROM @BI AS t
      CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
      CROSS APPLY x.nodes('/root/*') AS t2(c)
    UNION ALL
    SELECT Src = 2
        , longitem 
        , c.value('local-name(.)','SYSNAME') AS [Key]
        , c.value('text()[1]','VARCHAR(MAX)') AS [Value]
    FROM @CUST AS t
      CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
      CROSS APPLY x.nodes('/root/*') AS t2(c)
)
SELECT longitem
   , [key] AS [column]
   , Org_Value = MAX(CASE WHEN Src = 1 THEN Value END)
   , New_Value = MAX(CASE WHEN Src = 2 THEN Value END)
FROM rs
GROUP BY longitem, [key]
HAVING COALESCE(MAX(CASE WHEN Src = 1 THEN Value END),'')
     <> COALESCE(MAX(CASE WHEN Src = 2 THEN Value END),'')
ORDER BY longitem,[key];

    Saída

    longitem coluna Org_Value Novo valor
    33300000200010354003 corrente FSC - CONCEITOS DE LOJA DE FÁBRICA FSC
    33300000200160033601 color_desc MÉD DOT STRP DOT STRP
    33300000200160033601 sku 29310505 293XXX05
    33300000200790414200 color_id 41 111
    • 1

relate perguntas