SQL Hoe te controleren of twee tabellen precies dezelfde gegevens hebben?

Ik heb twee tabellen TableAen TableBmet hetzelfde formaat van kolommen, wat betekent dat beide tabellen de kolommen hebben

A B C D E F 

Waar Aen Bzijn de primaire sleutels.

Hoe schrijf ik een SQL-query om te controleren of TableAen TableB(die dezelfde primaire toetsen hebben) Precies dezelfde waarden in elke kolom?

Het betekent dat deze twee tabellen precies dezelfde gegevens hebben.


Antwoord 1, Autoriteit 100%

U moet in staat zijn om “minus” of “behalve” te kunnen “, afhankelijk van de smaak van SQL die wordt gebruikt door uw DBMS.

select * from tableA
minus
select * from tableB

Als de query geen rijen retourneert, zijn de gegevens precies hetzelfde.


Antwoord 2, Autoriteit 70%

Relationele operatoren gebruiken:

SELECT * FROM TableA
UNION 
SELECT * FROM TableB
EXCEPT 
SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;

Wijzig EXCEPTnaar MINUSvoor Oracle.

Iets kieskeurig punt: het bovenstaande is afhankelijk van de voorrang van de operator, die volgens de SQL-standaard implementatie afhankelijk is, dus YMMV. Het werkt voor SQL Server, waarvoor de voorrang is:

  1. uitdrukkingen tussen haakjes
  2. INTERSECT
  3. EXCEPTen UNIONEvalueerd van links naar rechts.

Antwoord 3, Autoriteit 25%

dietbuddha heeft een mooi antwoord. In gevallen waar je geen MINUS of BEHALVE hebt, is een optie om alle tabellen samen te voegen, te groeperen op alle kolommen en ervoor te zorgen dat er van alles twee is:

SELECT col1, col2, col3
FROM
(SELECT * FROM tableA
UNION ALL  
SELECT * FROM tableB) data
GROUP BY col1, col2, col3
HAVING count(*)!=2

Antwoord 4, autoriteit 10%

SELECT c.ID
FROM clients c
WHERE EXISTS(SELECT c2.ID 
FROM clients2 c2
WHERE c2.ID = c.ID);

Retourneert alle ID’s die hetzelfde zijn in beide tabellen. Verander EXISTS in NOT EXISTS om de verschillen te krijgen.


Antwoord 5, autoriteit 4%

Ik nam het script van onedaywhen en heb het aangepast om ook te laten zien uit welke tabel elk item afkomstig is.

DECLARE @table1 NVARCHAR(80)= 'table 1 name'
DECLARE @table2 NVARCHAR(80)= 'table 2 name'
DECLARE @sql NVARCHAR (1000)
SET @sql = 
'
SELECT ''' + @table1 + ''' AS table_name,* FROM
(
SELECT * FROM ' + @table1 + '
EXCEPT
SELECT * FROM ' + @table2 + '
) x
UNION 
SELECT ''' + @table2 + ''' AS table_name,* FROM 
(
SELECT * FROM ' + @table2 + '
EXCEPT
SELECT * FROM ' + @table1 + '
) y
'
EXEC sp_executesql @stmt = @sql

Antwoord 6, autoriteit 2%

alleen om te voltooien, een proces dat is opgeslagen met behulp van de methode behalve om 2 tabellen te vergelijken en resultaat te geven in dezelfde tabel met 3 foutenstatus, ADD, DEL, GAP
tabel moet dezelfde PK hebben, u declareert de 2 tabellen en velden om te vergelijken van 1 of beide tabellen

Gewoon zo gebruiken
ps_TableGap ‘tbl1′,’Tbl2′,’fld1,fld2,fld3′,’fld4’fld5’fld6’ (optioneel)

/****** Object:  StoredProcedure [dbo].[ps_TableGap]    Script Date: 10/03/2013 16:03:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       Arnaud ALLAVENA
-- Create date: 03.10.2013
-- Description: Compare tables
-- =============================================
create PROCEDURE [dbo].[ps_TableGap]
    -- Add the parameters for the stored procedure here
    @Tbl1 as varchar(100),@Tbl2 as varchar(100),@Fld1 as varchar(1000), @Fld2 as varchar(1000)= ''
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
--Variables
[email protected] = table 1
[email protected] = table 2
[email protected] = Fields to compare from table 1
[email protected] Fields to compare from table 2
Declare @SQL varchar(8000)= '' --SQL statements
Declare @nLoop int = 1 --loop counter
Declare @Pk varchar(1000)= '' --primary key(s) 
Declare @Pk1 varchar(1000)= '' --first field of primary key
declare @strTmp varchar(50) = '' --returns value in Pk determination
declare @FldTmp varchar (1000) = '' --temporarily fields for alias calculation
--If @Fld2 empty we take @Fld1
--fields rules: fields to be compare must be in same order and type - always returns Gap
If @Fld2 = '' Set @Fld2 = @Fld1
--Change @Fld2 with Alias prefix xxx become _xxx 
while charindex(',',@Fld2)>0
begin
    Set @FldTmp = @FldTmp + (select substring(@Fld2,1,charindex(',',@Fld2)-1) + ' as _' + substring(@Fld2,1,charindex(',',@Fld2)-1) + ',')
    Set @Fld2 = (select ltrim(right(@Fld2,len(@Fld2)-charindex(',',@Fld2))))
end
Set @FldTmp = @FldTmp + @Fld2 + ' as _' + @Fld2
Set @Fld2 = @FldTmp
--Determinate primary key jointure
--rule: same pk in both tables
Set @nLoop = 1
Set @SQL = 'Declare crsr cursor for select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '''
 + @Tbl1 + ''' or TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 +  ''' or TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 
 + ''' order by ORDINAL_POSITION'
exec(@SQL)
open crsr 
fetch next from crsr into @strTmp
while @@fetch_status = 0
begin 
    if @nLoop = 1 
    begin 
        Set @Pk = 's.' + @strTmp + ' = b._' + @strTmp
        Set @Pk1 = @strTmp
        set @nLoop = @nLoop + 1 
    end 
    Else
    Set @Pk = @Pk + ' and s.' + @strTmp + ' = b._' + @strTmp
fetch next from crsr into @strTmp 
end 
close crsr
deallocate crsr
--SQL statement build
set @SQL = 'select case when s.' + @Pk1 + ' is null then ''Del'' when b._' + @Pk1 + ' is null then ''Add'' else ''Gap'' end as TypErr, '''
set @SQL = @SQL + @Tbl1 +''' as Tbl1, s.*, ''' + @Tbl2 +''' as Tbl2 ,b.* from (Select ' + @Fld1 + ' from ' + @Tbl1
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld2 + ' from ' + @Tbl2 + ')s full join (Select ' + @Fld2 + ' from ' + @Tbl2 
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld1 + ' from ' + @Tbl1 +')b on '+ @Pk 
--Run SQL statement
Exec(@SQL)
END

Antwoord 7, Autoriteit 2%

Verbetering op het antwoord van Dietbuddha …

select * from
(
    select * from tableA
    minus
    select * from tableB
)
union all
select * from
(
    select * from tableB
    minus
    select * from tableA
)

Antwoord 8, Autoriteit 2%

Bron: Gebruik natuurlijke volledige verbinding om twee tabellen in SQL te vergelijken met Lukas Eder

Slimme aanpak om NATURAL FULL JOINte gebruiken om dezelfde/verschillende rijen tussen twee tabellen te detecteren.

Voorbeeld 1 – statusvlag:

SELECT t1.*, t2.*, CASE WHEN t1 IS NULL OR t2 IS NULL THEN 'Not equal' ELSE 'Equal' END
FROM t1
NATURAL FULL JOIN t2;

Voorbeeld 2 – rijen filteren

SELECT *
FROM (SELECT 't1' AS t1, t1.* FROM t1) t1 
NATURAL FULL JOIN (SELECT 't2' AS t2, t2.* FROM t2) t2 
WHERE t1 IS NULL OR t2 IS NULL -- show differences
--WHERE  t1 IS NOT NULL AND t2 IS NOT NULL    -- show the same

db<>fiddle-demo


Antwoord 9

U kunt verschillen van 2 tabellen vinden door de combinatie van insert all en full outer join in Oracle. In sql kun je de verschillen extraheren via een volledige outer join, maar het lijkt erop dat insert all/first niet bestaat in sql! Daarom moet u in plaats daarvan de volgende zoekopdracht gebruiken:

select * from A
full outer join B on
A.pk=B.pk
where A.field1!=B.field1
or A.field2!=B.field2 or A.field3!=B.field3 or A.field4!=B.field4 
--and A.Date==Date1

Hoewel het gebruik van ‘OF’ in de where-clausule niet wordt aanbevolen en dit meestal leidt tot lagere prestaties, kunt u de bovenstaande query nog steeds gebruiken als uw tabellen niet enorm zijn.
Als er een resultaat is voor de bovenstaande query, zijn het precies de verschillen van 2 tabellen op basis van vergelijking van velden 1,2,3,4. Om de queryprestaties te verbeteren, kunt u deze ook op datum filteren (controleer het gedeelte met opmerkingen)


Antwoord 10

   SELECT unnest(ARRAY[1,2,2,3,3]) 
    EXCEPT
    SELECT unnest(ARRAY[1,1,2,3,3])
UNION
    SELECT unnest(ARRAY[1,1,2,3,3])
    EXCEPT
    SELECT unnest(ARRAY[1,2,2,3,3])

Resultaat is nul, maar bronnen zijn anders!

Maar:

(
    SELECT unnest(ARRAY[1,2,2,3])
    EXCEPT ALL
    SELECT unnest(ARRAY[2,1,2,3])
)
UNION
(
    SELECT unnest(ARRAY[2,1,2,3])
    EXCEPT ALL
    SELECT unnest(ARRAY[1,2,2,3])
)

werkt.


Antwoord 11

Ik had hetzelfde probleem in SQL Server en schreef dit T-SQL-script om het proces te automatiseren (eigenlijk is dit de verwaterde versie, de mijne schreef alle diff naar een enkele tabel voor gemakkelijke rapportage).

Update ‘MyTable’ en ‘MyOtherTable’ naar de namen van de tabellen die u wilt vergelijken.

DECLARE @ColName varchar(100)
DECLARE @Table1 varchar(100) = 'MyTable'
DECLARE @Table2 varchar(100) = 'MyOtherTable'
IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
SELECT  IDENTITY(INT, 1, 1) RowNum , c.name
INTO    #col
FROM    SYS.Objects o 
        JOIN SYS.columns c on o.object_id = c.object_id
WHERE   o.name = @Table1 AND NOT c.Name IN ('List','Columns','YouWantToIgnore')
DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)
    WHILE @Counter > 0
        BEGIN
            SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
                EXEC ('SELECT  t1.Identifier
                        ,t1.'[email protected]+' AS '[email protected][email protected]+'
                        ,t2.'[email protected]+' AS '[email protected][email protected]+'
                FROM    '[email protected]+' t1
                        LEFT JOIN '[email protected]+' t2 ON t1.Identifier = t2.Identifier 
                WHERE   t1.'[email protected]+' <> t2.'[email protected])
            SET @Counter = @Counter - 1 
        END

Antwoord 12

Ik heb dit geschreven om de resultaten te vergelijken van een behoorlijk nare weergave die ik heb overgezet van Oracle naar SQL Server. Het creëert een paar tijdelijke tabellen, #DataVariances en #SchemaVariances, met verschillen in (je raadt het al) de gegevens in de tabellen en het schema van de tabellen zelf.

Het vereist dat beide tabellen een primaire sleutel hebben, maar u kunt deze in tempdb plaatsen met een identiteitskolom als de brontabellen er geen hebben.

declare @TableA_ThreePartName nvarchar(max) = ''
declare @TableB_ThreePartName nvarchar(max) = ''
declare @KeyName nvarchar(max) = ''
/***********************************************************************************************
    Script to compare two tables and return differneces in schema and data.
    Author: Devin Lamothe       2017-08-11
***********************************************************************************************/
set nocount on
-- Split three part name into database/schema/table
declare @Database_A nvarchar(max) = (
    select  left(@TableA_ThreePartName,charindex('.',@TableA_ThreePartName) - 1))
declare @Table_A nvarchar(max) = (
    select  right(@TableA_ThreePartName,len(@TableA_ThreePartName) - charindex('.',@TableA_ThreePartName,len(@Database_A) + 2)))
declare @Schema_A nvarchar(max) = (
    select  replace(replace(@TableA_ThreePartName,@Database_A + '.',''),'.' + @Table_A,''))
declare @Database_B nvarchar(max) = (
    select  left(@TableB_ThreePartName,charindex('.',@TableB_ThreePartName) - 1))
declare @Table_B nvarchar(max) = (
    select  right(@TableB_ThreePartName,len(@TableB_ThreePartName) - charindex('.',@TableB_ThreePartName,len(@Database_B) + 2)))
declare @Schema_B nvarchar(max) = (
    select  replace(replace(@TableB_ThreePartName,@Database_B + '.',''),'.' + @Table_B,''))
-- Get schema for both tables
declare @GetTableADetails nvarchar(max) = '
    use [' + @Database_A +']
        select  COLUMN_NAME
             ,  DATA_TYPE
          from  INFORMATION_SCHEMA.COLUMNS
         where  TABLE_NAME = ''' + @Table_A + '''
           and  TABLE_SCHEMA = ''' + @Schema_A + '''
    '
create table #Table_A_Details (
    ColumnName nvarchar(max)
,   DataType nvarchar(max)
)
insert into #Table_A_Details
exec (@GetTableADetails)
declare @GetTableBDetails nvarchar(max) = '
    use [' + @Database_B +']
        select  COLUMN_NAME
             ,  DATA_TYPE
          from  INFORMATION_SCHEMA.COLUMNS
         where  TABLE_NAME = ''' + @Table_B + '''
           and  TABLE_SCHEMA = ''' + @Schema_B + '''
    '
create table #Table_B_Details (
    ColumnName nvarchar(max)
,   DataType nvarchar(max)
)
insert into #Table_B_Details
exec (@GetTableBDetails)
-- Get differences in table schema
            select  ROW_NUMBER() over (order by
                        a.ColumnName
                    ,   b.ColumnName) as RowKey
                 ,  a.ColumnName as A_ColumnName
                 ,  a.DataType as A_DataType
                 ,  b.ColumnName as B_ColumnName
                 ,  b.DataType as B_DataType
              into  #FieldList
              from  #Table_A_Details a
   full outer join  #Table_B_Details b
                on  a.ColumnName = b.ColumnName
             where  a.ColumnName is null
                or  b.ColumnName is null
                or  a.DataType <> b.DataType
        drop table  #Table_A_Details
        drop table  #Table_B_Details
            select  coalesce(A_ColumnName,B_ColumnName) as ColumnName
                 ,  A_DataType
                 ,  B_DataType
              into  #SchemaVariances
              from  #FieldList
-- Get differences in table data
declare @LastColumn int = (select max(RowKey) from #FieldList)
declare @RowNumber int = 1
declare @ThisField nvarchar(max)
declare @TestSql nvarchar(max)
create table #DataVariances (
    TableKey            nvarchar(max)
,   FieldName           nvarchar(max)
,   TableA_Value        nvarchar(max)
,   TableB_Value        nvarchar(max)
)
delete from #FieldList where A_DataType in ('varbinary','image') or B_DataType in ('varbinary','image') 
while @RowNumber <= @LastColumn begin
    set @TestSql = '
        select  coalesce(a.[' + @KeyName + '],b.[' + @KeyName + ']) as TableKey
             ,  ''' + @ThisField + ''' as FieldName
             ,  a.[' + @ThisField + '] as [TableA_Value]
             ,  b.[' + @ThisField + '] as [TableB_Value]
          from  [' + @Database_A + '].[' + @Schema_A + '].[' + @Table_A + '] a 
    inner join  [' + @Database_B + '].[' + @Schema_B + '].[' + @Table_B + '] b
            on  a.[' + @KeyName + '] = b.[' + @KeyName + ']
         where  ltrim(rtrim(a.[' + @ThisField + '])) <> ltrim(rtrim(b.[' + @ThisField + ']))
            or (a.[' + @ThisField + '] is null and  b.[' + @ThisField + '] is not null)
            or (a.[' + @ThisField + '] is not null and  b.[' + @ThisField + '] is null)
'
insert into #DataVariances
exec (@TestSql)
set @RowNumber = @RowNumber + 1
set @ThisField = (select coalesce(A_ColumnName,B_ColumnName) from #FieldList a where RowKey = @RowNumber)
end
drop table #FieldList
print 'Query complete.  Select from #DataVariances to verify data integrity or #SchemaVariances to verify schemas match.  Data types varbinary and image are not checked.'

Antwoord 13

SELECT * 
FROM TABLE A
WHERE NOT EXISTS (SELECT 'X' 
                  FROM  TABLE B 
                  WHERE B.KEYFIELD1 = A.KEYFIELD1 
                  AND   B.KEYFIELD2 = A.KEYFIELD2 
                  AND   B.KEYFIELD3 = A.KEYFIELD3)
;

‘X’ is een willekeurige waarde.

Verwissel de tabellen om de verschillende verschillen te zien.

Zorg ervoor dat u de belangrijkste velden in uw tabellen samenvoegt.

Of gebruik gewoon de MINUS-operator met 2 select-instructies, maar MINUS werkt alleen in Oracle.


Antwoord 14

De meeste reacties lijken het probleem van Kamil te negeren. (Dat is waar de tabellen identieke rijen bevatten, maar verschillende worden in elke tabel herhaald.) Helaas kan ik zijn oplossing niet gebruiken, omdat ik in Oracle zit. Het beste dat ik heb kunnen bedenken is:

SELECT * FROM
   (
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableA
   GROUP BY column1, column2, ...
   MINUS
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableB
   GROUP BY column1, column2, ...
   )
UNION ALL
   (
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableB
   GROUP BY column1, column2, ...
   MINUS
   SELECT column1, column2, ..., COUNT(*) AS the_count
   FROM tableA
   GROUP BY column1, column2, ...
   )

Antwoord 15

T1(PK, A, B) en T2(PK, A, B) vergelijken.

Vergelijk eerst primaire sleutelsets om te zoeken naar ontbrekende sleutelwaarden aan beide kanten:

SELECT T1.*, T2.* FROM T1 FULL OUTER JOIN T2 ON T1.PK=T2.PK WHERE T1.PK IS NULL OR T2.PK IS NULL;

Vermeld vervolgens alle niet-overeenkomende waarden:

SELECT T1.PK, 'A' AS columnName, T1.A AS leftValue, T2.A AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.A,0) != COALESCE(T2.A,0)
UNION ALL
SELECT T1.PK, 'B' AS columnName, T1.B AS leftValue, T2.B AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.B,0) != COALESCE(T2.B,0)

A en B moeten van hetzelfde type zijn. U kunt INFORMATION SCHEMA gebruiken om het SELECT. Vergeet de COALESCE niet om ook IS NULL-resultaten op te nemen. U kunt ook FULL OUTER JOIN en COALESCE(T1.PK,0)=COALESCE(T2.PK,0) gebruiken.

Bijvoorbeeld voor kolommen van het type varchar:

SELECT concat('SELECT T1.PK, ''', COLUMN_NAME, ''' AS columnName, T1.', COLUMN_NAME, ' AS leftValue, T2.', COLUMN_NAME, ' AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE COALESCE(T1.',COLUMN_NAME, ',0)!=COALESCE(T2.', COLUMN_NAME, ',0)')
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='T1' AND DATA_TYPE IN ('nvarchar','varchar');

Antwoord 16

We kunnen gegevens uit twee tabellen van DB2-tabellen vergelijken met de onderstaande eenvoudige query,

Stap 1:- Selecteer welke kolommen we moeten vergelijken uit tabel (T1) van schema(S)

    SELECT T1.col1,T1.col3,T1.col5 from S.T1

Stap 2:- Gebruik het trefwoord ‘Minus’ om 2 tabellen te vergelijken.

Stap 3:- Selecteer welke kolommen we moeten vergelijken uit tabel (T2) van schema(S)

    SELECT T2.col1,T2.col3,T2.col5 from S.T1

EIND resultaat:

    SELECT T1.col1,T1.col3,T1.col5 from S.T1
     MINUS 
     SELECT T2.col1,T2.col3,T2.col5 from S.T1;

Als de zoekopdracht geen rijen retourneert, zijn de gegevens precies hetzelfde.


Antwoord 17

In SQL Server… Het aantal rijen gebruiken en dit vervolgens vergelijken met het aantal rijen van het snijpunt:

DECLARE @t1count int = (SELECT COUNT(*) FROM table1)
IF (@t1count = (SELECT COUNT(*) FROM table2))  
   IF (SELECT COUNT (*) FROM (SELECT * FROM table1 INTERSECT SELECT * FROM table2) AS dT) = @t1count 
     SELECT 'Equal' 
   ELSE SELECT 'Not equal'
ELSE
 SELECT 'Not equal'

Ik heb het op deze manier geschreven, zodat wanneer het aantal rijen van de tabellen niet gelijk is, het snijpunt volledig wordt overgeslagen, wat in die gevallen de prestaties zal verbeteren.


Antwoord 18

Probeer dit

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1'
intersect
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table2';

Antwoord 19

In MySQL, waar “min” niet wordt ondersteund, en rekening houdend met de prestaties, is dit een snelle

query:
SELECT 
t1.id, 
t1.id 
FROM t1 inner join t2 using (id) where concat(t1.C, t1.D, ...)<>concat(t2.C, t2.D, ...)

Antwoord 20

Een alternatieve, verbeterde zoekopdracht gebaseerd op het antwoord van dietbuddha & IanMc.
De query bevat een beschrijving om handig te laten zien waar rijen bestaan en ontbreken. (NB: voor SQL Server)

(
    select 'InTableA_NoMatchInTableB' as Msg, * from tableA
    except
    select 'InTableA_NoMatchInTableB' , * from tableB
)
union all
(
    select 'InTableB_NoMatchInTableA' as Msg, * from tableB
    except
    select 'InTableB_NNoMatchInTableA' ,* from tableA
)

Other episodes