I have created a SQL Stored Procedure to compare any two tables based on Microsoft SQL Server 2005 new syntax EXCEPT and INTERCEPT. Basically, I used EXCEPT and UNION to get the result of differences between tables and INTERCEPT to get the same result of two tables.
In addition to that, the SP will compare two tables by specifying column fields and conditions and display the result by optional ORDER BY clause. Here is the SP:
CREATE procedure [dbo].[SP_CompareTables] (
@table1 varchar(100),
@table2 varchar(100),
@table_colList varchar(3000) = NULL,
@whereClause varchar(3000) = NULL,
@orderByClause varchar(3000) = NULL,
@difference0 int = 0
)
AS
DECLARE @sql varchar(8000);
DECLARE @colList varchar(3000);
BEGIN
if ( @table_colList is null Or @table_colList = '' )
begin
set @colList = '*';
end
else
begin
set @colList = @table_colList;
end
if ( @difference0 = 0 )
begin
set @sql =REPLACE(REPLACE(REPLACE('
SELECT ''@table1'' AS TblName, *
FROM (
SELECT @colList
FROM @table1
EXCEPT (
SELECT @colList
FROM @table2)
) x
UNION ALL
SELECT ''@table2'' AS TblName, *
FROM (
SELECT @colList
FROM @table2
EXCEPT (
SELECT @colList
FROM @table1)
) y',
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
end;
else
begin
set @sql =REPLACE(REPLACE(REPLACE('
SELECT @colList
FROM @table1
INTERSECT (
SELECT @colList
FROM @table2)',
'@table1', @table1),
'@table2', @table2),
'@colList', @colList);
end;
if ( @whereClause is not null And len(@whereClause) > 0 )
begin
set @sql = REPLACE(REPLACE('
SELECT * FROM (@sql) v
WHERE @whereClause',
'@sql', @sql),
'@whereClause', @whereClause);
end
if ( @orderByClause is not null And len(@orderByClause) > 0 )
begin
set @sql = REPLACE(REPLACE('@sql
ORDER BY @orderByClause',
'@sql', @sql),
'@orderByClause', @orderByClause);
end;
print @sql;
exec(@sql);
return 0;
END
To use this SP is very simple. For example, to compare two tables of [Employees] and [Employees_backup], you can just run the following script to compare two whole tables:
EXEC SP_CompareTables 'Employees', 'Employees_backup';
More examples by specifying columns, where clause and order clause:
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName',
'FirstName like ''D%'' AND BirthDate Between ''Jan 01, 1990'' AND ''Jul 30, 2008''';
EXEC SP_CompareTables 'Employees', 'Employees_backup', 'FirstName, LastName',
NULL, 'FirstName, BirthDate' , 1; -- get same results
Posts
