Finding duplicates rows in a table

[Gayatree]

I have to concatenate 2 colimns in a table and find duplicates in them.
I already used the method
select * from tableA a
where (select count(*) from TableA b
where acol1+ +col2 = b.col1+ +col2)>1
But the performance is very bad.
Data is also huge
Can you help me
Thanks in advance

[ck9663]

I have to concatenate 2 colimns in a table and find duplicates in them.
I already used the method
select * from tableA a
where (select count(*) from TableA b
where acol1+ +col2 = b.col1+ +col2)>1
But the performance is very bad.
Data is also huge
Can you help me
Thanks in advance

try this to show the duplicates.

select a.*
from tableA a
inner join (select col1, col2 from tableA b group by col1, col2 having count(*) > 1) s on a.col1 = s.col1 and s.col2 = a.col2
 

Happy coding!

-- CK

[Gayatree]

Hi ck9663,
Thank u for u r immediate reply.
But when I run the below query

select Col1 + + Col2
from TableA
group by Col1 + + Col2
having(count(*) )>1

I should get the duplicates .
But the the duplicate which I got from the query which u suggested is
different from the above qurey.
Is the above query wrong?
can u please help me
Thanks in advance

[ck9663]

try:

select Col1, Col2
from TableA
group by Col, Col2
having count(*) >1

By the way, use the tags when posting.

-- CK

[Gayatree]

Hi CK
Thank u for u r reply.
The 2 query's which u have suggested r working well.
But The count which both of them return should be the same.
But I am getting 1000 more records with the first one.

Next time when i post a query I will use tags

Can u please help me
If I am not clear with the question me please let me know
Thank u in advance

[ck9663]

This query will give the duplicate:

select Col1, Col2, count(*) as cnt
from TableA
group by Col, Col2
having count(*) >1
 

Look at cnt column. If it says "2" it means you have two records on your table with the same col1 and col2. This means it will return the unique combination of the two columns and the number of rows that are duplicate. Not the actual duplicate rows. Make sense?

-- CK

[Gayatree]

YUP ,GOT U

Thank u So much.

[Gayatree]

Sorry for troubling you again.
I am loading the data using SSIS.
If there r 2 duplicates only one should be loaded into the
target table ,the remaining should go into another table.
Is this possible in SSIS.

Thank you for helping me and giving me all the above query's
Thank you

[ck9663]

Am not sure if it's possible in SSIS.

What you could do is to create a trigger on your target table. If the value you're trying to insert on your table is already existing, insert it to another table and rollback the transaction.

On T-SQL, it would've been different. Depending on your case, a DISTINCT might be sufficient enough. If not, you might need to further analyze your table and establish an algorithm on which record to keep and which record to throw.

-- CK

[Gayatree]

Thank you for u r immedaite answer.
I will try creating trigger.

In SSMS,when we right click on the table
we will get options as script table as create to ...ect
But for me alter to is disabled,what should I do to enable that.
why will that be disabled when I have permissions to devlop in that box
and even execute is disabled.

Can u please help me

Thank you in advance