tnagasatish
asked on
Intersection and minus in MSSQL
I have nearly 50 pairs of tables. Each table has nearly 20 columns. I would like to find out the number of rows that are common in each pair of tables. I also would like to findout the count of minus operation. I cannot use EXISTS and NOT EXISTS, because there are 20 columns, and 50 tables. I know that there is no direct operator for intersection, and minus in MSSQL. Is there any hacky way to findout this without writing complex program.
i agree with hilaire...
you could always generate the sql for the tables by reference to information schema columns/tables
have you considered
Select x.*
From (
select 'tab1' as tabnam,a.* from table1 as A
union
select 'tab2' as tabnam,b.* from table2 as b
) as x
Group by the list of 20 columns
having count(distinct Tabnam) = 2
would find the duplicates
you could always generate the sql for the tables by reference to information schema columns/tables
have you considered
Select x.*
From (
select 'tab1' as tabnam,a.* from table1 as A
union
select 'tab2' as tabnam,b.* from table2 as b
) as x
Group by the list of 20 columns
having count(distinct Tabnam) = 2
would find the duplicates
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you telling us that there's no primary key / unique indexes defined on your tables ?
To perform INTERSECT :
make 2 sub queries and make an inner join between the two
To perform Minus
make 2 sub queries and make a full outer join between the two, filtering to keep only
rows from a where b.column is null
and
rows from b where a.column is null
Don't think of anything else for the moment ...
Hilaire