[dba-SQLServer] Duplicate records

Mark A Matte markamatte at hotmail.com
Tue Mar 18 15:46:01 CDT 2008


Hello All,

I have a large table that occasionally duplicates make it into.  The 2 fields that make the record unique are Case_ID and Note_Seq_Nbr.  The duplicates are identical...so I have no primary key.  I used the code below.  It doesn't run very fast...but it does the job of removing just the extra records...

Any thoughts,comments, or suggestions on making it faster/better...or a totally different approach?

Thanks,

Mark A. Matte



**********************BEGIN************************
--By Mark A. Matte 3/18/2008
declare @CaseID decimal(15,0), at NoteNum int, at NoteCount int
declare MyCursor cursor
for Select case_id,note_seq_nbr,count(*) as CT
	from tblCaseNote_temp
	group by case_id,note_seq_nbr
	having count(*)>1
	order by case_id,note_seq_nbr
open mycursor
fetch next from mycursor
into @CaseID, at NoteNum, at NoteCount
while (@@fetch_status =0)

Begin
	delete top(@noteCount-1) from tblCaseNote_Temp where case_id=@CaseID and note_seq_nbr=@noteNum
	fetch next from mycursor
	into @CaseID, at NoteNum, at NoteCount
End
Close mycursor
deallocate mycursor
***********************END*************************
_________________________________________________________________
Need to know the score, the latest news, or you need your Hotmail®-get your "fix".
http://www.msnmobilefix.com/Default.aspx



More information about the dba-SQLServer mailing list