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
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.
The stored procedure helps in comparing two tables:
Create Procedure usp_CompareTable
-- Variables to hold the dynamic query
Declare @TotalRowsFirstTable nchar(100),
-- Variables to hold the row count of different tables
Declare @RowCountFirstTable int,
-- Variable which would hold the result of the comparison. 0 = Mismatch, 1 = Match
Declare @result bit
SET @TotalRowsFirstTable = 'Select @RowCountFirstTable=count(*) From ' + @FirstTableName
N'@RowCountFirstTable Int OUTPUT',
@RowCountFirstTable = @RowCountFirstTable OUTPUT
SET @TotalRowsSecondTable = 'Select @RowCountSecondTable=count(*) From ' + @SecondTableName
N'@RowCountSecondTable Int OUTPUT',
SET @TotalRowsUnion = 'Select @RowCountUnion=count(*) From (Select * From ' + @FirstTableName + ' Union Select * From ' + @SecondTableName + ') as AliasSecond '
N'@RowCountUnion INT OUTPUT',
If (@RowCountFirstTable = @RowCountSecondTable) and (@RowCountSecondTable = @RowCountUnion)
Set @result = 1
Print 'Success: Tables are matching'
Set @result = 0
Print 'Error: Tables are not matching'
Execute usp_CompareTable 'student', 'studentcopy'