pesquisa sql para devolver as diferenças entre duas tabelas
estou a tentar comparar duas tabelas, o servidor SQL, para verificar alguns dados. Eu quero devolver todas as linhas de ambas as tabelas onde os dados estão em uma ou na outra. Em essência, quero mostrar todas as discrepâncias. Preciso de verificar três dados ao fazê-lo, o primeiro nome, O Último Nome e o produto.
Sou novo na SQL e parece que muitas das soluções que estou a encontrar São sobre complicar as coisas. Não tenho de me preocupar com NULLs. Comecei por tentar algo. assim:SELECT DISTINCT [First Name], [Last Name], [Product Name] FROM [Temp Test Data]
WHERE ([First Name] NOT IN (SELECT [First Name]
FROM [Real Data]))
editar:
baseado na resposta de @treaschf tenho tentado usar uma variação da seguinte consulta:
SELECT td.[First Name], td.[Last Name], td.[Product Name]
FROM [Temp Test Data] td FULL OUTER JOIN [Data] AS d
ON td.[First Name] = d.[First Name] AND td.[Last Name] = d.[Last Name]
WHERE (d.[First Name] = NULL) AND (d.[Last Name] = NULL)
mas continuo a receber 0 resultados de volta, quando sei que há pelo menos uma linha em td que não está em d.
editar:
Está bem, acho que descobri. Pelo menos nos meus poucos minutos de testes, parece funcionar bem o suficiente.SELECT [First Name], [Last Name]
FROM [Temp Test Data] AS td
WHERE (NOT EXISTS
(SELECT [First Name], [Last Name]
FROM [Data] AS d
WHERE ([First Name] = td.[First Name]) OR ([Last Name] = td.[Last Name])))
10 answers
SE você tiver tabelas A e B, ambos com colum C, aqui estão os registros, que estão presentes na tabela A, mas não em B:
SELECT A.*
FROM A
LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL
Para obter todas as diferenças com uma única consulta, uma junção completa deve ser usada, assim:
SELECT A.*, B.*
FROM A
FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL
O Que você precisa saber, neste caso, é que, quando o registro pode ser encontrada em A, mas não em B, que as colunas que vêm de B será NULO, e da mesma forma para aqueles, que estão presentes em B e não em A, o as colunas de A serão nulas.
( SELECT * FROM table1
EXCEPT
SELECT * FROM table2)
UNION ALL
( SELECT * FROM table2
EXCEPT
SELECT * FROM table1)
Eu sei que esta pode não ser uma resposta popular, mas eu concordo com @Randy Minder em usar ferramentas de terceiros quando é necessária uma comparação mais complexa.
Este caso específico aqui é fácil e para este caso tais ferramentas não são necessárias, mas isso pode ficar complexo facilmente se você introduzir mais colunas, bases de dados em dois servidores, critérios de comparação mais complexos e assim.
Existem muitas destas ferramentas, tais como ApexSQL Data Diff ou Quest Toad e você pode sempre use-os no modo de teste para fazer o trabalho feito.
Para obter todas as diferenças entre duas tabelas, pode usar como eu este pedido SQL:
SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2
) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1
) AS T2
;
Se quiser obter quais os valores das colunas que são diferentes, poderá usar o Modelo Entidade-atributo-valor:
declare @Data1 xml, @Data2 xml
select @Data1 =
(
select *
from (select * from Test1 except select * from Test2) as a
for xml raw('Data')
)
select @Data2 =
(
select *
from (select * from Test2 except select * from Test1) as a
for xml raw('Data')
)
;with CTE1 as (
select
T.C.value('../@ID', 'bigint') as ID,
T.C.value('local-name(.)', 'nvarchar(128)') as Name,
T.C.value('.', 'nvarchar(max)') as Value
from @Data1.nodes('Data/@*') as T(C)
), CTE2 as (
select
T.C.value('../@ID', 'bigint') as ID,
T.C.value('local-name(.)', 'nvarchar(128)') as Name,
T.C.value('.', 'nvarchar(max)') as Value
from @Data2.nodes('Data/@*') as T(C)
)
select
isnull(C1.ID, C2.ID) as ID, isnull(C1.Name, C2.Name) as Name, C1.Value as Value1, C2.Value as Value2
from CTE1 as C1
full outer join CTE2 as C2 on C2.ID = C1.ID and C2.Name = C1.Name
where
not
(
C1.Value is null and C2.Value is null or
C1.Value is not null and C2.Value is not null and C1.Value = C2.Value
)
EXEMPLO DE VIOLINO SQL
Tenta isto:
SELECT
[First Name], [Last Name]
FROM
[Temp Test Data] AS td EXCEPTION JOIN [Data] AS d ON
(d.[First Name] = td.[First Name] OR d.[Last Name] = td.[Last Name])
Isto vai fazer o truque, semelhante com a solução deTiago , devolver a tabela "fonte" também.
select [First name], [Last name], max(_tabloc) as _tabloc
from (
select [First Name], [Last name], 't1' as _tabloc from table1
union all
select [First name], [Last name], 't2' as _tabloc from table2
) v
group by [Fist Name], [Last name]
having count(1)=1
O resultado irá conter diferenças entre tabelas, na coluna _tabloc você terá a referência da tabela.
Para um simples teste de fumo onde está a tentar assegurar que duas tabelas coincidam com a preocupação com os nomes das colunas:
--ensure tables have matching records
Select count (*) from tbl_A
Select count (*) from tbl_B
--create temp table of all records in both tables
Select * into #demo from tbl_A
Union All
Select * from tbl_B
--Distinct #demo records = Total #demo records/2 = Total tbl_A records = total tbl_B records
Select distinct * from #demo
Você pode facilmente escrever um procedimento de loja para comparar um lote de tabelas.
Variação simples da resposta @erikkallen que mostra qual a tabela em que a linha está presente:
( SELECT 'table1' as source, * FROM table1
EXCEPT
SELECT * FROM table2)
UNION ALL
( SELECT 'table2' as source, * FROM table2
EXCEPT
SELECT * FROM table1)
Se tiver um erro
Todas as consultas combinadas usando uma união, INTERSECT ou excepto o Operador devem ter um número igual de expressões nas suas listas de destino.
Então pode ajudar a adicionar
( SELECT 'table1' as source, * FROM table1
EXCEPT
SELECT 'table1' as source, * FROM table2)
UNION ALL
( SELECT 'table2' as source, * FROM table2
EXCEPT
SELECT 'table2' as source, * FROM table1)
Há um problema de desempenho relacionado com a junção à esquerda, bem como a junção completa com grandes dados.
Na minha opinião, esta é a melhor solução:select [First Name], count(1) e from (select * from [Temp Test Data] union all select * from [Temp Test Data 2]) a group by [First Name] having e = 1