set nocount on
declare @Emp1 as Table(Id int,EName varchar(30))
insert into @Emp1 values(1,'one')
insert into @Emp1 values(2,'two')
insert into @Emp1 values(3,'three')
insert into @Emp1 values(4,'four')
insert into @Emp1 values(5,'five')
insert into @Emp1 values(6,'six')
insert into @Emp1 values(8,'eight')
declare @Emp2 as Table(Id int,EName varchar(30))
insert into @Emp2 values(1,'one')
insert into @Emp2 values(2,'two')
insert into @Emp2 values(3,'three')
insert into @Emp2 values(4,'four')
insert into @Emp2 values(6,'six')
insert into @Emp2 values(6,'six')
insert into @Emp2 values(7,'seven')
declare @Result as Table(Id int,EName varchar(30), [Table] varchar(30))
insert into @Result
SELECT *, 'Emp1' as [Table] FROM (SELECT * FROM @Emp1 EXCEPT SELECT * FROM @Emp2) AS T1
Union all
SELECT *, 'Emp2' as [Table] FROM (SELECT * FROM @Emp2 EXCEPT SELECT * FROM @Emp1) AS T2
set nocount off
if((select count(1) from @Result)>0)
begin
Print 'Not identical'
end
else
begin
Print 'identical'
end