SQL compares data from two tables

Question

I have 2 tables TableA and TableB that have the same column format, for example, both tables TableA and TableB have columns

 ABCDEF 

where A and B are primary keys.

How to write SQL to verify that if TableA and TableB having the same primary keys contain the same value in each column.

This means that these two tables have exactly the same data.

Answer 1

You should be able to use "MINUS" or "EXCEPT" depending on the taste of SQL used by your DBMS.

 select * from tableA minus select * from tableB 

If the query does not return rows, then the data will be exactly the same.

Answer 2

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 aforementioned relies on the priority of the statement, which, according to the SQL standard, is implementation dependent, therefore YMMV. It works for SQL Server, for which priority is:

• Expressions in parentheses
• INTERSECT
• EXCEPT and UNION are evaluated from left to right.

Answer 3

dietbuddha has a nice answer. In cases where you do not have MINUS or EXCEPT, one option is to do a join between tables, group by all columns and make sure that there are 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 

Answer 4

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

Will return all identifiers that are the same in both tables. To get the differences, EXISTS DOES NOT EXIST.

Answer 5

Taking a script from onedaywhen, I modified it to also show which table each record 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 

Answer 6

just to complete, proc saved using the except method to compare 2 tables and get the result in the same table with 3 errors, ADD, DEL, GAP table should have the same PK, you declare 2 tables and fields for comparison 1 or both tables

Just use 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)>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 

Answer 7

Strengthening the response to dietubaddha ...

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

Answer 8

You can find the differences between the two tables using a combination of insert all and full outer join in Oracle. In sql, you can extract the differences using a full outer join, but it seems that the all / first insert does not exist in sql! Therefore, you should use the 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 the where clause is not recommended and usually results in poor performance, you can still use the above query if your tables are not massive. If there is any result for the above query, these are exactly the differences between the two tables, based on a comparison of fields 1,2,3,4. To improve query performance, you can also filter it by date (see Commented section).

Answer 9

  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]) 

The result is null, but the 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]) ) 

work.

Answer 10

I had the same problem in SQL Server and I wrote this T-SQL script to automate the process (in fact, this is a version with a wet version, I wrote all the data for a separate table for easy reporting).

Update "MyTable" and "MyOtherTable" to the names of the tables you want 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 > 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+' <> t2.'+@ColName) SET @Counter = @Counter - 1 END 

Answer 11

I wrote this to compare the results of a rather nasty view that I ported from Oracle to SQL Server. It creates a pair of temporary tables, #DataVariances and #SchemaVariances, with differences in (you guessed it) the data in the tables and the layout of the tables themselves.

It requires both tables to have a primary key, but you could put it in tempdb using the identity column if the source tables do not have it.

 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.' 

Answer 12

Most of the answers seem to ignore the issue raised by Camille. (This is where the tables contain the same rows, but different tables are repeated in each table.) Unfortunately, I cannot use its solution because I'm in Oracle. The best I could come up with was:

 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, ... ) 

Answer 13

We can compare data from two DB2 table tables using the following simple query:

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

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

Step 2: - Use the keyword "Minus" to compare 2 tables.

Step 3: - Select which all columns we need to compare from table (T2) of scheme (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 does not return rows, the data is exactly the same.

Answer 14

In MySQL, where the minus is not supported and takes into account performance, this is fast

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

Answer 15

Alternative extended query based on answer from dietbuddha and IanMc. The query includes a description to help show where the 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 ) 

Answer 16

 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 tables to see different discrepancies.

Be sure to combine the key fields in your tables.

Or just use the MINUS statement with two select statements, however, MINUS can only work on Oracle.