Compare 2 tables field by field

3

I have two tables with equal fields. Table A and B

I need to compare the contents of the two tables field by field. I'm using the following code to find the records that have some different field, but I would like it to be shown in the record only the different field (s) if possible of course.

Here's an example:

SELECT nova.codigo   AS codigonv
      ,nova.nome     AS nomenv
      ,nova.celular  AS celularnv
      ,atual.codigo  AS codigoatual
      ,atual.nome    AS nomeatual
      ,atual.celular AS celularatual
  FROM contatonovo AS nv
  FULL JOIN contato AS atual
    ON (nv.codigo = atual.codigo)
 WHERE atual.codigo IS NULL
    OR atual.nome IS NULL
    OR atual.celular IS NULL

I thank you for your attention and goodwill.

    
asked by anonymous 18.06.2018 / 19:17

3 answers

1

The first query returns the rows that are different in current,

SELECT codigo, CASE WHEN q.nome = a.nome THEN NULL ELSE q.nome END nome,
               CASE WHEN q.celular = a.celular THEN NULL ELSE q.celular END celular
FROM (SELECT Nova.codigo, Nova.nome, Nova.celular
      FROM contatoNovo nova

      EXCEPT

      SELECT atual.codigo, atual.nome, atual.celular
      FROM contato atual) q
LEFT JOIN contato a ON a.codigo = q.codigo

and the second query, lines that are different in new

SELECT codigo, CASE WHEN q.nome = n.nome THEN NULL ELSE q.nome END nome,
               CASE WHEN q.celular = n.celular THEN NULL ELSE q.nome END celular
FROM (SELECT atual.codigo, atual.nome, atual.celular
      FROM contato atual

      EXCEPT

      SELECT Nova.codigo, Nova.nome, Nova.celular
      FROM contatoNovo nova) q
LEFT JOIN contatoNovo n ON n.codigo = q.codigo

To do the update / insert / delete, see the MERGE command: link

MERGE (Transact-SQL)

  

Performs insert, update, or delete operations on a target table based on the results of joining with the source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a table based on the differences found in the other table.

    
18.06.2018 / 19:24
0

You did not specify how the result should be displayed so I am proposing two options:

Using these records as an example:

create table contatonovo
(
       codigo  varchar(5) null
      ,nome    varchar(50) null
      ,celular varchar(10) null
)
GO
create table contato
(
       codigo  varchar(5) null
      ,nome    varchar(50) null
      ,celular varchar(10) null
)
GO

insert into contato values
 ('01','primeiro da silva', '1111-1111')
,('02','segundo contato', '2222-2222')
,('03','terceiro fulano', '3333-3333')
,('04','quarto sicrano', '4444-4444')
,('05','tudo igual', '5555-5555')

insert into contatonovo values
 ('01','primeiro da silva', null)
,('02','segundo contato da silva', '2222-2222')
,('03','terceiro fulano', '3003-3333')
,('04','quartos sicrano', null)
,('05','tudo igual', '5555-5555')
GO

You can show all records that have differences, showing each record once using UNION :

select nv.codigo, nv.nome as [Novo nome], at.nome as [Antigo nome], nv.celular as [Novo Celular], at.celular as [Antigo Celular]
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.nome,'') != coalesce(at.nome,'')
UNION
select nv.codigo, nv.nome as [Novo nome], at.nome as [Antigo nome], nv.celular as [Novo Celular], at.celular as [Antigo Celular]
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.celular,'') != coalesce(at.celular,'')

or showing a difference by logging this way by highlighting which field is different, again using UNION to put all together in a dataset only:

select nv.codigo, nv.nome as [Novo valor], at.nome as [Antigo valor], 'Nome' as [Diferença]
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.nome,'') != coalesce(at.nome,'')
UNION
select nv.codigo, nv.celular, at.celular, 'Celular'
from contatonovo nv
join contato at on at.codigo = nv.codigo
where coalesce(nv.celular,'') != coalesce(at.celular,'')

For the given example the results would be:

codigo Novo nome                 Antigo nome        Novo Celular Antigo Celular
------ ------------------------  ------------------ ------------ --------------
01     primeiro da silva         primeiro da silva  NULL         1111-1111
02     segundo contato da silva  segundo contato    2222-2222    2222-2222
03     terceiro fulano           terceiro fulano    3003-3333    3333-3333
04     quartos sicrano           quarto sicrano     NULL         4444-4444

or

codigo Novo valor                Antigo valor     Diferença
------ ------------------------- ---------------- ---------
01     NULL                      1111-1111        Celular
02     segundo contato da silva  segundo contato  Nome
03     3003-3333                 3333-3333        Celular
04     NULL                      4444-4444        Celular
04     quartos sicrano           quarto sicrano   Nome

Note: If you have Visual Studio it comes with a reasonable benchmarking tool that can compare Schema or Data.

    
18.06.2018 / 20:50
0
  

I need to compare two sql tables with equal fields but they can have different contents or one have more or less records than the other.   (...) I would like to see only the different field (s)

Here's another approach:

-- código #1
SELECT A.codigo, N.codigo,
       [nome atual]= case when coalesce(A.nome, '') <> coalesce(N.nome, '')
                          then A.nome else '' end,
       [nome novo]= case when coalesce(A.nome, '') <> coalesce(N.nome, '')
                         then N.nome else '' end,
       [celular atual]= case when coalesce(A.celular, '') <> coalesce(N.celular, '')
                             then A.celular else '' end,
       [celular novo]= case when coalesce(A.celular, '') <> coalesce(N.celular, '')
                            then N.celular else '' end,
       Observação= case when A.codigo is null and N.codigo is not null
                             then 'Contato incluído'
                        when A.codigo is not null and N.codigo is null
                             then 'Contato excluído' 
                        else '' end
  from CONTATONOVO as N
       full outer join CONTATO as A on A.codigo = N.codigo
  where coalesce(A.nome, '') <> coalesce(N.nome, '') 
        or coalesce(A.celular, '') <> coalesce(N.celular, '')
  order by coalesce(A.codigo, N.codigo);

The above code considers that the nome and% co_% columns contain string .

Code # 1 can be rewritten using CTE to improve readability:

-- código #2 v2
with CONTATOS as (
SELECT A.codigo as codigoA, N.codigo as codigoN,
       coalesce(A.nome, '') as nomeA, coalesce(N.nome, '') as nomeN,
       coalesce(A.celular, '') as celularA, coalesce(N.celular, '') as celularN
  from CONTATONOVO as N
       full outer join CONTATO as A on A.codigo = N.codigo
)
SELECT codigoA, codigoN,
       [nome atual]= case when nomeA <> nomeN then nomeA else '' end,
       [nome novo]= case when nomeA <> nomeN then nomeN else '' end,
       [celular atual]= case when celularA <> celularN then celularA else '' end,
       [celular novo]= case when celularA <> celularN then celularN else '' end,
       Observação= case when codigoA is null and codigoN is not null
                             then 'Contato incluído'
                        when codigoA is not null and codigoN is null
                             then 'Contato excluído' 
                        else '' end
  from CONTATOS
  where nomeA <> nomeN
        or celularA <> celularN
  order by coalesce(codigoA, codigoN);
    
20.06.2018 / 15:20