CODEWITHSUNDEEP
sql
Laura
Laura

Reputation:

Comparing two database tables

I need to compare ALL fields in two tables... they are the same size (four lines) and have the same amount of columns (five columns). The logic I'm looking for is...

If (table 1 = table 2)
do something
Else
do something else

In SQL I wrote something like...

If (Select * from table 1 = select * from table 2)
do something
else
do something else

THIS DOESN'T WORK!!!

I tried doing EXCEPT and UNION ALL statements... but I don't need to know the row that is different or even the values that are different, I just need to know BOOLEAN 'yes' the tables are different or 'no' they are not.

Upvotes: 1

Views: 16496

Answers (6)

Marco Lorenz
Marco Lorenz

Reputation: 1

being tired to write all the columns new every time I expanded Wiretaps code.

"exec CompareTables table1, table2" will do the Job easier:

CREATE PROCEDURE [dbo].[CompareTables](@table1 varchar(100), 

 @table2 Varchar(100))

AS



-- Table1, Table2 are the tables or views to compare.

-- The columns of both tables are acquired from the table definition
-- ordered by the ordinal position 

-- The result is all rows from either table that do NOT match

-- the other table in all columns specified, along with which table that

-- row is from.



declare @SQL varchar(8000);
declare @T1ColumnList varchar(1000);

SET @T1ColumnList = (
Select TOP 1 Stuff(
        (
        Select ',' + C.COLUMN_NAME
        From INFORMATION_SCHEMA.COLUMNS As C
        Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
            And C.TABLE_NAME = T.TABLE_NAME
        Order By C.ORDINAL_POSITION
        For Xml Path('')
        ), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T WHERE T.TABLE_NAME=@table1)
declare @T2ColumnList varchar(1000);
SET @T2ColumnList = (
Select TOP 1 Stuff(
        (
        Select ',' + C.COLUMN_NAME
        From INFORMATION_SCHEMA.COLUMNS As C
        Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
            And C.TABLE_NAME = T.TABLE_NAME
        Order By C.ORDINAL_POSITION
        For Xml Path('')
        ), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T WHERE T.TABLE_NAME=@table2)



set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +

 ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +

 @t2ColumnList + ' FROM ' + @Table2



set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +

 ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + 

 ' HAVING COUNT(*) = 1'



exec ( @SQL)


GO

Upvotes: 0

van
van

Reputation: 76952

If you want to compare only few columns (or tables with 2-5 columns), you may use this FULL JOIN (not tested):

select      COUNT(*) AS UnmatchedRows
from        table1 t1
full join   table2 t2
        on  t1.col1 = t2.col1
        and t1.col2 = t2.col2
        and t1.col3 = t2.col3
        and t1.col4 = t2.col4
        and t1.col5 = t2.col5
where       COALESCE(t1.col1, t2.col1) IS NULL

Still the solution referenced by Druid is very cool.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40289

[Revised]

Here's how you'd do this with "SELECT...EXCEPT...":

IF not exists(select *
               from MyTable
              except select *
               from MyOtherTable)
 and not exists(select *
               from MyOtherTable
              except select *
               from MyTable)
    PRINT 'They match'
ELSE
    PRINT 'They do not match'

A bit quicker to write, unless you need to compare fewer than all the columns. This will require four table scans, so you should compare and contrast performance with the UNION strategies presented. In my experience SELECT...EXCEPT... tends to run very quickly -- I'd guess because all the unioning and column comparing is occuring internally.

Upvotes: 1

Wiretap
Wiretap

Reputation: 781

Something like this should work, using Exist/Not Exists - then it's up to you how to interpret either getting or not getting a row back as True or False. The actual syntax will depend on the database. This is Transact-SQL

Create table A
( one int, two int, three int , four int)

Create table B
( one int, two int, three int, four int)

insert A values ( 1,2,3,4)
insert B values( 1,2,3,4)

select * from A a
where exists ( select 1 from B b where 
    a.one = b.one
and a.two = b.two
and a.three = b.three
and a.four = b.four)

one two three four

1 2 3 4

Upvotes: 0

Christopher Klein
Christopher Klein

Reputation: 2793


ALTER PROCEDURE dbo.CompareTables
(
    @table1 VARCHAR(100), 
    @table2 VARCHAR(100), 
    @T1ColumnList VARCHAR(1000),
    @T2ColumnList VARCHAR(1000) = ''
)
AS
/*
    Table1, Table2 are the tables or views to compare.
    T1ColumnList is the list of columns to compare, from table1.
    Just list them comma-separated, like in a GROUP BY clause.
    If T2ColumnList is not specified, it is assumed to be the same
    as T1ColumnList.  Otherwise, list the columns of Table2 in
    the same order as the columns in table1 that you wish to compare.



The result is all rows from either table that do NOT match
the other table in all columns specified, along with which table that
row is from.




*/



DECLARE @SQL VARCHAR(8000)



IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList



SET @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
 ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
 @t2ColumnList + ' FROM ' + @Table2



SET @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
 ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + 
 ' HAVING COUNT(*) = 1'



EXEC ( @SQL)

Upvotes: 0

Druid
Druid

Reputation: 6453

This blog post explains how to do this:

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION

Upvotes: 9

Related Questions