Ways to compare and find differences for SQL Server tables data



We got requirement to compare tables data from 2 different sources to know what was changed.

To do same we have used except command, you can also use left join
For example:
/*** To find extra rows********/
select * from emp1 left join emp2 on emp1.id =emp2.id
/*** OR  ********/
select * from emp1
except
select * from emp2

Below is demo to achieve same along source code:

/*************Creating dummy data for demo***********************/
USE tempdb
GO
CREATE TABLE [dbo].[emp1](  [id] [nchar](10) NOT NULL,  [name] [nchar](10) NULL,  [age] [nchar](10) NULL )
GO
insert into [dbo].[emp1] values
(1,'Saurabh',30), (2,'Pradeep',25),(3,'Rupa',26),(4,'Parul',30)
GO
----(3 row(s) affected)
CREATE TABLE [dbo].[emp2](  [id] [nchar](10) NOT NULL, [name] [nchar](10) NULL, [age] [nchar](10) NULL )
GO
insert into [dbo].[emp2] values
(1,'Saurabh',30), (2,'Pradeep',25),(3,'Rupa',26)
GO
/*** To find extra rows********/
select * from emp1 left join emp2 on emp1.id =emp2.id
/*** To find extra rows********/
select * from emp1
except
select * from emp2
/************************************/



1 comment:

  1. Thanks for the post. It's really useful. Please find the below given blog url and share your knowledge and experience with us.
    https://sqlgiants.blogspot.in

    ReplyDelete