Advertisement
Advertisement


SQL compare data from two tables


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.

2013/09/11
1
86
9/11/2013 1:08:20 PM


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:

  1. Expressions in parentheses
  2. INTERSECT
  3. EXCEPT and UNION evaluated from left to right.
2013/09/11

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
2016/01/28

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.

2016/08/09

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
2016/05/19

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
[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
2013/10/03

Source: https://stackoverflow.com/questions/4602083
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]