SQL how to check that two tables has exactly the same data

Question

I have 2 tables TableA and TableB which have the same format of column for example both tables TableA and TableB have columns  A B C D E F    where A and B are the primary keys   How to write SQL to check that if TableA and TableB that have the same primary keys contains exactly the same value in every columns    It means that these two tables has exactly the same data

User · Answer

We can compare data from two tables of DB2 tables using the below simple query,

Step 1:- Select which all columns we need to compare from table (T1) of schema(S)

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

Step 2:- Use 'Minus' keyword for comparing 2 tables.

Step 3:- Select which all columns we need to compare from table (T2) of schema(S)

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

END result:

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

If the query returns no rows then the data is exactly the same.

User · Answer

Using relational operators   SELECT   FROM TableA UNION  SELECT   FROM TableB EXCEPT  SELECT   FROM TableA INTERSECT SELECT   FROM TableB    Change EXCEPT to MINUS for Oracle    Slightly picky point  the above relies on operator precedence  which according to the SQL Standard is implementation dependent  so YMMV  It works for SQL Server  for which the precedence is    Expressions in parentheses INTERSECT EXCEPT and UNION evaluated from left to right

User · Answer

SELECT c ID FROM clients c WHERE EXISTS SELECT c2 ID  FROM clients2 c2 WHERE c2 ID   c ID     Will return all ID s that are the SAME in both tables  To get the differences change EXISTS to NOT EXISTS

User · Answer

Source  Use NATURAL FULL JOIN to compare two tables in SQL by Lukas Eder Clever approach of using NATURAL FULL JOIN to detect the same different rows between two tables  Example 1 - status flag  SELECT t1    t2    CASE WHEN t1 IS NULL OR t2 IS NULL THEN  Not equal  ELSE  Equal  END FROM t1 NATURAL FULL JOIN t2   Example 2 - filtering rows 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 lt  gt fiddle demo

User · Answer

An alternative  enhanced query based on answer by dietbuddha  amp  IanMc  The query includes description to helpfully show where rows exist and are missing   NB  for 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

User · Answer

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     Result is null  but sources are different   But         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       works

User · Answer

You should be able to  MINUS  or  EXCEPT  depending on the flavor of SQL used by your DBMS     select   from tableA minus select   from tableB   If the query returns no rows then the data is exactly the same

User · Answer

I wrote this to compare the results of a pretty nasty view I ported from Oracle to SQL Server   It creates a pair of temp tables   DataVariances and  SchemaVariances  with differences in  you guessed it  the data in the tables and the schema of the tables themselves   It requires both tables have a primary key  but you could drop it into tempdb with an identity column if the source tables don t have one   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  lt  gt  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  lt    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         lt  gt  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

User · Answer

Most of the responses seem to ignore the issue raised by Kamil   That is where the tables contain identical rows  but different ones are repeated in each table    Unfortunately  I am not able to use his solution  because I am in Oracle  The best I ve been able to come up with 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

User · Answer

You can find differences of 2 tables using combination of insert all and full outer join in Oracle  In sql you can extract the differences via full outer join but it seems that insert all first doesnt exist in sql  Hence  you have to use following query instead   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   Although using  OR  in where clause is not recommended and it usually yields in lower performance  you can still use above query if your tables are not massive  If there is any result for the above query  it is exactly the differences of 2 tables based on comparison of fields 1 2 3 4  For improving the query performance  you can filter it by date as well check the commented part

User · Answer

In MySQL  where  minus  is not supported  and taking performance into account  this is a fast   query  SELECT  t1 id   t1 id  FROM t1 inner join t2 using  id  where concat t1 C  t1 D       lt  gt concat t2 C  t2 D

User · Answer

just to complet  a proc stored using except method to compare 2 tables and give result in same table with 3 errors status  ADD  DEL  GAP table must have same PK  you declare the 2 tables and fields to compare of 1 or both table  Just use like this  ps TableGap  tbl1   Tbl2   fld1 fld2 fld3   fld4 fld5 fld6   optional           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 -- Tbl1   table 1 -- Tbl2   table 2 -- Fld1   Fields to compare from table 1 -- Fld2 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  gt 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

User · Answer

To compare T1 PK  A  B  and T2 PK  A  B    First compare primary key sets to look for missing key values on either side   SELECT T1    T2   FROM T1 FULL OUTER JOIN T2 ON T1 PK T2 PK WHERE T1 PK IS NULL OR T2 PK IS NULL    Then list all value mismatch   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 and B must be of same type  You can use INFORMATION SCHEMA to generate the SELECT  Don t forget the COALESCE to also include IS NULL results  You could also use FULL OUTER JOIN and COALESCE T1 PK 0  COALESCE T2 PK 0    For example for columns of 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

User · Answer

dietbuddha has a nice answer   In cases where you don t have a MINUS or EXCEPT   one option is to do a union all between the tables  group by with all the columns and make sure there is two of everything   SELECT col1  col2  col3 FROM  SELECT   FROM tableA UNION ALL   SELECT   FROM tableB  data GROUP BY col1  col2  col3 HAVING count     2

User · Answer

I had this same issue in SQL Server and wrote this T-SQL script to automate the process  actually this is the watered-down version  mine wrote all the diff to a single table for easy reporting     Update  MyTable  and  MyOtherTable  to the names of the tables you wish to compare   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  gt  0          BEGIN             SET  ColName    SELECT name FROM  Col WHERE RowNum   Counter                  EXEC   SELECT  t1 Identifier                          t1    ColName   AS    Table1  ColName                            t2    ColName   AS    Table2  ColName                   FROM       Table1   t1                         LEFT JOIN    Table2   t2 ON t1 Identifier   t2 Identifier                  WHERE   t1    ColName    lt  gt  t2    ColName              SET  Counter    Counter - 1          END

User · Answer

Enhancement to dietbuddha s answer     select   from       select   from tableA     minus     select   from tableB   union all select   from       select   from tableB     minus     select   from tableA

User · Answer

Taking the script from onedaywhen  I modified it to also show which table each entry comes from   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

User · Answer

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 any value   Switch the tables to see the different discrepancies   Make sure to join the key fields in your tables   Or just use the MINUS operator with 2 select statements  however  MINUS can only work in Oracle

[sql] SQL how to check that two tables has exactly the same data?

Examples related to sql