June 22, 2009 at 2:37 pm
I am doing a table comparision on 2 tables where in i have 2 million records in each table using..
Select Col1,Col2....Col48
EXCEPT
Select Col1,Col2....Col48
It is taking more the 2 hrs to get the result, is there a way to get quich result set.
June 22, 2009 at 3:17 pm
do you need to compare every single column or a subset of them like pk would do the trick?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 22, 2009 at 4:00 pm
every single column except some getdate fields as anyways they would differ.
June 22, 2009 at 6:01 pm
From central forum itself:
First:
You can write union all then write union query order by the primary key. you can see the difference.
select primarykey as Primarykey,* from T1
union
select primarykey as Primarykey,* from T2
order by Primarykey
if this query return exact rows then both table have same data if it return more than exact rows then again run
select primarykey as Primarykey,* from T1
union ALL
select primarykey as Primarykey,* from T2
order by Primarykey
put that output into a temp table then run group by (on Primarykey) query you will find the differance.
MJ
June 23, 2009 at 11:17 am
I do not have any PK on my tables except that we have clustered index.
June 23, 2009 at 11:38 am
If it is daily work..., You can go for tool redgate sql compare http://www.redgate.com.
There is trail version to compare the data..
I was using in my previous company it is too good..
Rajesh Kasturi
June 24, 2009 at 6:34 am
Tara (6/23/2009)
I do not have any PK on my tables except that we have clustered index.
- A unique key would also do just fine !
- strange .... a large table without a PK/UK
- Maybe a checksum operation may be a valid alternative.
Select T.*
From TableA T.
inner join (
Select Identifier_Column(s), CHECKSUM ( * ) as CSum
from tableA
except
Select Identifier_Column(s), CHECKSUM ( * ) as CSum
from tableX
) Diff
on T.Identifier_Column(s) = Diff.Identifier_Column(s)
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 24, 2009 at 6:37 am
Tara (6/23/2009)
I do not have any PK on my tables except that we have clustered index.
You really should add a primary key to those tables to avoid problems like this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply