Comparing tables in SQL Server 2K

While using SQL Server 2000, 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. 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'