Monday, June 10, 2013

how to get different rows from tableA with compare of tableB sql server


Using the new EXCEPT operator we can find out which rows in tableA do not exist in tableB.
It will only work with 2005 or latter version of SQL Server 2005.


create table tableX



(
 
column1 int,

column2 int



)
 
insert into tableX

select 1,1

union all

select 1,2

union all

select 1,3

union all

select 2,1

create table tableY



(
 
column1 int,

column2 int



)
 
insert into tableY

select 1,1

union all

select 1,3

union all

select 2,2



go
 
select *

from tableX



EXCEPT
 
select *

from tableY

The above statement is equals to following statement

select column1, column2
from   tableX
where not exists (select  *
                             from     tableY
                             where   tableX.column1 = tableY.column1
                                 and   tableX.column2 = tableY.column2)


Thanks
Mahesh kumar sharma

No comments: