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,'Sushant',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,'Sushant',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
/************************************/
Thanks for the post. It's really useful. Please find the below given blog url and share your knowledge and experience with us.
ReplyDeletehttps://sqlgiants.blogspot.in