[dba-SQLServer] Duplicate records

Cursors are slow, you could choose to place an index to prevent duplicates
so duplicate information just never makes it into the table?



On Tue, Mar 18, 2008 at 1:46 PM, Mark A Matte <markamatte at hotmail.com>
wrote:

>
> 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(R)-get
> your "fix".
> http://www.msnmobilefix.com/Default.aspx
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...