finding duplicate records?

Question

Post reply

finding duplicate records?

Charmer

SSChampion

Points: 14903
More actions
March 12, 2012 at 10:23 am

#258537

HI friends,
i am using merge statement to insert and update some records between two tables ..
both of the table are same structure..i am just synchronizing the tables..
when i try to do that, it gives violation of duplicate error...there are five columns which is represented as unique index...
primary key is not involved in the index..both of the table is having same primary key ID, but index is different...so there is no new record is going to insert, but some how it gives error with index columns. I want to see which record is giving error..
help me by Giving some SQL statements to show that error giving records
Thanks,
Charmer

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

366 points! You are not first-timer here.

Please try to post your question as per "forum etiquette", link at the bottom of my signature.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Charmer SSChampion Points: 14903 More actions · Answer 2

Eugene Elutin (3/12/2012)
366 points! You are not first-timer here.
Please try to post your question as per "forum etiquette", link at the bottom of my signature.

sorry about that..i was in a hurry..o.k let me try to get some sample data posted over here...

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Charmer (3/13/2012)
Eugene Elutin (3/12/2012)
366 points! You are not first-timer here.
Please try to post your question as per "forum etiquette", link at the bottom of my signature.
sorry about that..i was in a hurry..o.k let me try to get some sample data posted over here...

Too much in a hurry to type sql server find duplicates in Google?

Typing this question in a forum takes even more time...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pallavi.unde SSCommitted Points: 1987 More actions · Answer 4

Use Rownumber() it will create a virtual column of separate id for each row.. then by using that id you can update any duplicate record.

and its bad practice if your table is not having primary key. 😛

Thanks & Regards,
Pallavi

manub22 Hall of Fame Points: 3163 More actions · Answer 5

Check this blog post to identify and delete duplicate records from your tables in various ways: http://sqlwithmanoj.wordpress.com/2011/10/14/identify-delete-duplicate-records-from-a-table/[/url]

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 6

when i try to do that, it gives violation of duplicate error...there are five columns which is represented as unique index..., but index is different...

If your syncing the two table the unique index on these tables need to be the same.

Check the definition of the indexes and then compare the column to identify which column is missing in the index definition of the source table.

e.g if indx1 consists of columns A, B , C, D and E

and indx2 consists of A,B,D,E,F

then u know that a combination of values ABCD is not creating the required uniqueness.

Jayanth Kurup[/url]

Charmer SSChampion Points: 14903 More actions · Answer 7

Friends,

Some one temme how to add files over here...?

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Charmer (3/15/2012)
Friends,
Some one temme how to add files over here...?

Add files where? Your email attachment?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 9

Koen Verbeeck (3/15/2012)
Charmer (3/15/2012)
Friends,
Some one temme how to add files over here...?
Add files where? Your email attachment?

Yes Koen

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

You mean the attachments here at SQLServerCentral?

When you create a new post, you have a button Edit Attachments in the Post Options a bit down.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

You can just place your code surrounded with special tags which you can see on the left side of the text box where you're typing your post...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Charmer SSChampion Points: 14903 More actions · Answer 12

Koen Verbeeck (3/15/2012)
You mean the attachments here at SQLServerCentral?
When you create a new post, you have a button Edit Attachments in the Post Options a bit down.

yes i got it....i have never rolled down the screen before...that was my problem...sorry about that....Thanks to Koen and Eugene

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 13

pallavi.unde (3/13/2012)
Use Rownumber() it will create a virtual column of separate id for each row.. then by using that id you can update any duplicate record.
and its bad practice if your table is not having primary key. 😛

Thank you..for your suggestion

Thanks,
Charmer