Comparing tables ...

There are times when we would like to check whether the content in two tables are same or not. As of now there isn’t any built-in function in SQL Server to do the same (who knows they might come up with something in Yukon!). At present we need to manually compare the contents of tables to find out whether they are matching or not. Won’t it be nice to have a stored procedure which would do the job for us? Read on …

Setting the environment

First let us create a test table and populate it with some test data.

Create table Student
(
[name] varchar(50),
[age] int
)

Insert into Student ([name],age) Values ('Vadivel',27)
Insert into Student ([name],age) Values ('Ash',30)

Let us now create a copy of this table with a new name:

Select * into StudentCopy from Student

Now both the table ‘Student’ and ‘StudentCopy’ has the same structure and values.

Solution!!

The stored procedure helps in comparing two tables:

Create Procedure usp_CompareTable
(
@FirstTableName varchar(128),
@SecondTableName varchar(128)
)
As

-- Variables to hold the dynamic query
Declare @TotalRowsFirstTable nchar(100),
@TotalRowsSecondTable nchar(100),
@TotalRowsUnion nchar(100)

-- Variables to hold the row count of different tables
Declare @RowCountFirstTable int,
@RowCountSecondTable int,
@RowCountUnion int

-- Variable which would hold the result of the comparison. 0 = Mismatch, 1 = Match
Declare @result bit
SET @TotalRowsFirstTable = 'Select @RowCountFirstTable=count(*) From ' + @FirstTableName

Exec SP_EXECUTESQL
@TotalRowsFirstTable OUTPUT,
N'@RowCountFirstTable Int OUTPUT',
@RowCountFirstTable = @RowCountFirstTable OUTPUT

SET @TotalRowsSecondTable = 'Select @RowCountSecondTable=count(*) From ' + @SecondTableName

Exec SP_EXECUTESQL
@TotalRowsSecondTable OUTPUT,
N'@RowCountSecondTable Int OUTPUT',
@RowCountSecondTable=@RowCountSecondTable OUTPUT

SET @TotalRowsUnion = 'Select @RowCountUnion=count(*) From (Select * From ' + @FirstTableName + ' Union Select * From ' + @SecondTableName + ') as AliasSecond '

Exec SP_EXECUTESQL
@TotalRowsUnion OUTPUT,
N'@RowCountUnion INT OUTPUT',
@RowCountUnion=@RowCountUnion OUTPUT

If (@RowCountFirstTable = @RowCountSecondTable) and (@RowCountSecondTable = @RowCountUnion)

Begin
Set @result = 1
Print 'Success: Tables are matching'
End
Else
Begin
Set @result = 0
Print 'Error: Tables are not matching'
End
GO

To Test

Execute usp_CompareTable 'student', 'studentcopy'