sql query to return differences between two tables

Question

I am trying to compare two tables  SQL Server  to verify some data  I want to return all the rows from both tables where data is either in one or the other  In essence  I want to show all the discrepancies  I need to check three pieces of data in doing so  FirstName  LastName and Product   I m fairly new to SQL and it seems like a lot of the solutions I m finding are over complicating things  I don t have to worry about NULLs   I started by trying something like this   SELECT DISTINCT  First Name    Last Name    Product Name  FROM  Temp Test Data  WHERE   First Name  NOT IN  SELECT  First Name   FROM  Real Data      I m having trouble taking this further though   Thanks   EDIT   Based on the answer by  treaschf I have been trying to use a variation of the following query   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    But I keep getting 0 results back  when I know that there is at least 1 row in td that is not in d   EDIT   Ok  I think I figured it out  At least in my few minutes of testing it seems to work good enough   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       This is basically going to tell me what is in my test data that is not in my real data  Which is completely fine for what I need to do

User · Answer

There is a performance issue related with the left join as well as full join with large data.

In my opinion this is the best solution:

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

User · Answer

I know that this may not be a popular answer but I do agree with  Randy Minder on using third party tool when more complex comparison is needed   This specific case here is easy and for this case such tools are not needed but this can get complex easily if you introduce more columns  databases on two servers  more complex comparison criteria and such    There are a lot of these tools such as ApexSQL Data Diff or Quest Toad and you can always use them in trial mode to get the job done

User · Answer

Presenting the Cadillac of Diffs as an SP   See within for the basic template that was based on answer by  erikkallen   It supports   Duplicate row sensing  most other answers here do not  Sort results by argument Limit to specific columns  Ignore columns  e g  ModifiedUtc  Cross database tables names Temp tables  use as workaround to diff views    Usage   exec Common usp DiffTableRows   t1     t2    exec Common usp DiffTableRows       pTable0             ydb ysh table1        pTable1             xdb xsh table2        pOrderByCsvOpt     null   -- Order the results      pOnlyCsvOpt        null   -- Only compare these columns      pIgnoreCsvOpt      null   -- Ignore these columns  ignored if  pOnlyCsvOpt is specified    Code   alter proc  Common   usp DiffTableRows           pTable0          varchar 300        pTable1          varchar 300        pOrderByCsvOpt   nvarchar 1000    null   -- Order the Results      pOnlyCsvOpt      nvarchar 4000    null   -- Only compare these columns      pIgnoreCsvOpt    nvarchar 4000    null   -- Ignore these columns  ignored if  pOnlyCsvOpt is specified       pDebug           bit   0 as   ---------------------------------------------------------------------------------------------------------------------     Purpose   Compare rows between two tables         Usage   exec Common usp DiffTableRows   a     b        Modified    By          Description     ----------  ----------  -------------------------------------------------------------------------------------------     2015 10 06  crokusek    Initial Version     2019 03 13  crokusek    Added  pOrderByCsvOpt     2019 06 26  crokusek    Support for  pIgnoreCsvOpt   pOnlyCsvOpt          2019 09 04  crokusek    Minor debugging improvement     2020 03 12  crokusek    Detect duplicate rows in either source table   ---------------------------------------------------------------------------------------------------------------------   begin try      if  substring  pTable0  1  1                 set  pTable0    tempdb       pTable0  -- object id test below needs full names for temp tables      if  substring  pTable1  1  1                 set  pTable1    tempdb       pTable1  -- object id test below needs full names for temp tables      if  object id  pTable0  is null          raiserror  Table name is not recognized     s     16  1   pTable0        if  object id  pTable1  is null          raiserror  Table name is not recognized     s     16  1   pTable1        create table  ColumnGathering               Name nvarchar 300  not null          Sequence int not null          TableArg tinyint not null             declare          usp          varchar 100    object name   procid                sql          nvarchar 4000            sqlTemplate  nvarchar 4000                             use  database                insert into  ColumnGathering             select Name  column id as Sequence   TableArg  as TableArg               from sys columns c              where object id   object id    table       U                              set  sql   replace replace replace  sqlTemplate            TableArg    0             database     select DatabaseName from Common ufn SplitDbIdentifier  pTable0               table     pTable0        if   pDebug   1          print  Sql  CG 0       sql       exec sp executesql  sql       set  sql   replace replace replace  sqlTemplate            TableArg    1             database     select DatabaseName from Common ufn SplitDbIdentifier  pTable1               table     pTable1        if   pDebug   1          print  Sql  CG 1       sql       exec sp executesql  sql       if   pDebug   1          select   from  ColumnGathering       select Name              min Sequence  as Sequence              convert bit  iif min TableArg    0  1  0   as InTable0             convert bit  iif max TableArg    1  1  0   as InTable1       into  Columns       from  ColumnGathering      group by Name     having        pOnlyCsvOpt is not null               and Name in  select Value from Common ufn UsvToNVarcharKeyTable  pOnlyCsvOpt  default            or                    pOnlyCsvOpt is null              and  pIgnoreCsvOpt is not null               and Name not in  select Value from Common ufn UsvToNVarcharKeyTable  pIgnoreCsvOpt  default            or                    pOnlyCsvOpt is null              and  pIgnoreCsvOpt is null       if  exists  select 1 from  Columns where InTable0   0 or InTable1   0       begin         select 1  -- without this the debugging info doesn t stream sometimes         select   from  Columns order by Sequence                  waitfor delay  00 00 02    -- give results chance to stream before raising exception         raiserror  Columns are not equal between tables  consider using args  pIgnoreCsvOpt   pOnlyCsvOpt   See Result Sets for details    16  1           end      if   pDebug   1          select   from  Columns order by Sequence       declare           columns nvarchar 4000    --iif  pOnlyCsvOpt is null and  pIgnoreCsvOpt is null             --                                       select substring  select       ac name                 from  Columns ac                order by Sequence                  for xml path      2 200000  as csv                     if   pDebug   1      begin         print  Columns       columns          waitfor delay  00 00 02    -- give results chance to stream before possibly raising exception     end      -- Based on https   stackoverflow com a 2077929 538763     --     - Added sensing for duplicate rows     --     - Added reporting of source table location     --     set  sqlTemplate                 with                 a as  select    Row Number   over  partition by   order by  select null   -1 as Duplicates from  a                    b as  select    Row Number   over  partition by   order by  select null   -1 as Duplicates from  b               select 0 as SourceTable                  from                                        select   from a                    except                    select   from b                     anb               union all              select 1 as SourceTable                   from                                        select   from b                    except                    select   from a                     bna              order by  orderBy                       set  sql   replace replace replace replace  sqlTemplate                 a     pTable0                  b     pTable1                     columns                 orderBy    coalesce  pOrderByCsvOpt   columns      SourceTable                    if   pDebug   1          print  Sql       sql        exec sp executesql  sql   end try begin catch     declare                  CatchingUsp  varchar 100    object name   procid            if  xact state     -1          rollback           -- Disabled for S O  post      --exec Common usp Log         -- pMethod    CatchingUsp       --exec Common usp RethrowError                 -- pCatchingMethod    CatchingUsp       throw  end catch go  create function Common Trim        pOriginalString nvarchar max         pCharsToTrim nvarchar 50    null -- specify null or  default  for whitespae      returns table with schemabinding as    --------------------------------------------------------------------------------------------------     Purpose    Trim the specified characters from a string       Modified    By              Description     ----------  --------------  --------------------------------------------------------------------     2012 09 25  S Rutszy crok   Modified from https   dba stackexchange com a 133044 9415       --------------------------------------------------------------------------------------------------    return with cte AS     select patindex N        EffCharsToTrim   N       pOriginalString  AS  FirstChar            patindex N        EffCharsToTrim   N      reverse  pOriginalString   AS  LastChar            len  pOriginalString   N     - 1 AS  ActualLength     from               select EffCharsToTrim   coalesce  pCharsToTrim  nchar 0x09    nchar 0x20    nchar 0x0d    nchar 0x0a        c   select substring  pOriginalString   FirstChar                      cte  ActualLength  -  LastChar   -  FirstChar    2           AS  TrimmedString         --        --cte  ActualLength          -- FirstChar          --  cte  ActualLength  -  LastChar     1  AS  LastChar                from cte  go  create function  Common   ufn UsvToNVarcharKeyTable         pCsvList     nvarchar MAX        pSeparator   nvarchar 1              -- can pass keyword  default  when calling using    s               --     -- SQL Server 2012 distinguishes nvarchar keys up to maximum of 450 in length  900 bytes      --      returns  tbl table  Value nvarchar 450  not null primary key Value   as   -------------------------------------------------------------------------------------------------     Purpose   Converts a comma separated list of strings into a sql NVarchar table   From                http   www programmingado net a-398 SQL-Server-parsing-CSV-into-table aspx                     This may be called from RunSelectQuery                     GRANT SELECT ON Common ufn UsvToNVarcharTable TO MachCloudDynamicSql       Modified    By              Description     ----------  --------------  -------------------------------------------------------------------     2011 07 13  internet        Initial version     2011 11 22  crokusek        Support nvarchar strings and a custom separator      2017 12 06  crokusek        Trim leading and trailing whitespace from each element      2019 01 26  crokusek        Remove newlines   -------------------------------------------------------------------------------------------------        begin     declare           pos      int           textpos  int           chunklen smallint           str      nvarchar 4000            tmpstr   nvarchar 4000            leftover nvarchar 4000            csvList nvarchar max    iif  pSeparator not in  char 13   char 10   char 13    char 10                replace replace  pCsvList  char 13        char 10                     pCsvList   -- remove newlines      set  textpos   1     set  leftover            while  textpos  lt   len  csvList      begin         set  chunklen   4000 - len  leftover          set  tmpstr   ltrim  leftover   substring  csvList   textpos   chunklen           set  textpos    textpos    chunklen          set  pos   charindex  pSeparator   tmpstr          while  pos  gt  0         begin             set  str   substring  tmpstr  1   pos - 1              set  str    select TrimmedString from Common Trim  str  default                insert  tbl  value  values  str               set  tmpstr   ltrim substring  tmpstr   pos   1  len  tmpstr                set  pos   charindex  pSeparator   tmpstr          end          set  leftover    tmpstr     end      -- Handle  leftover      set  str    select TrimmedString from Common Trim  leftover  default         if  str  lt  gt            insert  tbl  value  values  str        return end GO  create function Common ufn SplitDbIdentifier  pIdentifier nvarchar 300   returns  table table            InstanceName          nvarchar 300  not null      DatabaseName          nvarchar 300  not null      SchemaName            nvarchar 300       BaseName              nvarchar 300  not null      FullTempDbBaseName    nvarchar 300              -- non-null for tempdb  e g   Abc             InstanceWasSpecified  bit not null      DatabaseWasSpecified  bit not null      SchemaWasSpecified    bit not null      IsCurrentInstance     bit not null      IsCurrentDatabase     bit not null      IsTempDb              bit not null      OrgIdentifier         nvarchar 300  not null   as   -----------------------------------------------------------------------------------------------------------     Purpose   Split a Sql Server Identifier into its parts  providing appropriate default values and               handling temp table  tempdb  references       Example   select   from Common ufn SplitDbIdentifier  t                 union all               select   from Common ufn SplitDbIdentifier  s t                 union all               select   from Common ufn SplitDbIdentifier  d s t                 union all               select   from Common ufn SplitDbIdentifier  i d s t                 union all               select   from Common ufn SplitDbIdentifier   d                 union all               select   from Common ufn SplitDbIdentifier  tempdb   d                    -- Empty               select   from Common ufn SplitDbIdentifier  illegal name          Modified    By              Description     ----------  --------------  -----------------------------------------------------------------------------     2013 09 27  crokusek        Initial version      -----------------------------------------------------------------------------------------------------------   begin     declare           name nvarchar 300    ltrim rtrim  pIdentifier         -- Return an empty table as a  throw      --     --Removed for SO post     --if  Common ufn IsSpacelessLiteralIdentifier  name    0        --  return       -- Find dots starting from the right by reversing first       declare           revName nvarchar 300    reverse  name        declare          firstDot int   charindex       revName        declare          secondDot  int   iif  firstDot   0   0  charindex       revName   firstDot   1         declare          thirdDot   int   iif  secondDot   0  0  charindex       revName   secondDot   1         declare          fourthDot  int   iif  thirdDot   0  0  charindex       revName   thirdDot   1         --select  firstDot   secondDot   thirdDot   fourthDot  len  name        -- Undo the reverse    first dot is first from the right       --     set  firstDot   iif  firstDot   0  0  len  name  -  firstDot   1       set  secondDot   iif  secondDot   0  0  len  name  -  secondDot   1       set  thirdDot   iif  thirdDot   0  0  len  name  -  thirdDot   1       set  fourthDot   iif  fourthDot   0  0  len  name  -  fourthDot   1        --select  firstDot   secondDot   thirdDot   fourthDot  len  name        declare          baseName   nvarchar 300     substring  name   firstDot   1  len  name  -  firstdot        declare          schemaName nvarchar 300    iif  firstDot -  secondDot - 1  lt   0                                           null                                          substring  name   secondDot   1   firstDot -  secondDot - 1        declare          dbName     nvarchar 300    iif  secondDot -  thirdDot - 1  lt   0                                           null                                          substring  name   thirdDot   1   secondDot -  thirdDot - 1        declare          instName   nvarchar 300    iif  thirdDot -  fourthDot - 1  lt   0                                           null                                           substring  name   fourthDot   1   thirdDot -  fourthDot - 1         with input as           select            coalesce  instName          servername        as InstanceName             coalesce  dbName      iif left  baseName  1          tempdb   db name     as DatabaseName             coalesce  schemaName  iif left  baseName  1          dbo   schema name     as SchemaName              baseName as BaseName             iif left  baseName  1                                            select  name  from tempdb sys objects                   where object id   object id  tempdb       baseName                                    null  as FullTempDbBaseName                             iif  instName is null  0  1  InstanceWasSpecified                    iif  dbName is null  0  1  DatabaseWasSpecified             iif  schemaName is null  0  1  SchemaWasSpecified                 insert into  table                 select i InstanceName  i DatabaseName  i SchemaName  i BaseName  i FullTempDbBaseName              i InstanceWasSpecified  i DatabaseWasSpecified  i SchemaWasSpecified              iif i InstanceName           servername        1  0  as IsCurrentInstance              iif i DatabaseName   db name    1  0  as IsCurrentDatabase              iif left  baseName  1         1  0  as IsTempDb               name as OrgIdentifier        from input i       return  end GO

User · Answer

Try this    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     Much simpler to read

User · Answer

SELECT   FROM table1     EXCEPT     SELECT   FROM table2    UNION ALL     SELECT   FROM table2     EXCEPT     SELECT   FROM table1

User · Answer

If you want to get which column values are different  you could use Entity-Attribute-Value model   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     SQL FIDDLE EXAMPLE

User · Answer

IF you have tables A and B  both with colum C  here are the records  which are present in table A but not in B   SELECT A   FROM A     LEFT JOIN B ON  A C   B C  WHERE B C IS NULL   To get all the differences with a single query  a full join must be used  like this   SELECT A    B   FROM A     FULL JOIN B ON  A C   B C  WHERE A C IS NULL OR B C IS NULL   What you need to know in this case is  that when a record can be found in A  but not in B  than the columns which come from B will be NULL  and similarly for those  which are present in B and not in A  the columns from A will be null

User · Answer

To get all the differences between two tables  you can use like me this SQL request    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

User · Answer

You can use except   for example something like this    -- DB1  Tb1 have values than DB2  Tb1 not have Select Col1 Col2 Col3 From DB1  Tb1 except Select Col1 Col2 Col3 From DB2  Tb1 -- Now we change order -- DB2  Tb1 have values than DB1  Tb1 not have Select Col1 Col2 Col3 From DB2  Tb1 except Select Col1 Col2 Col3 From DB1  Tb1

User · Answer

For a simple smoke test where you you re trying to ensure two tables match w out worrying about column names   --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    You can easily write a store procedure to compare a batch of tables

User · Answer

Simple variation on  erikkallen answer that shows which table the row is present in       SELECT  table1  as source    FROM table1     EXCEPT     SELECT   FROM table2    UNION ALL     SELECT  table2  as source    FROM table2     EXCEPT     SELECT   FROM table1       If you get an error      All queries combined using a UNION  INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists    then it may help to add       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

User · Answer

This will do the trick  similar with Tiago s solution  return  source  table as well    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   result will contain differences between tables  in column  tabloc you will have table reference

[sql] sql query to return differences between two tables

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2005