Databases
StackOverflow2013
Postsdbo
POde-duplicating rows in a sql server 2005 table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POde-duplicating rows in a sql server 2005 table
    text
    copied!<p>I have a table with ~17 million rows in it. I need to de-duplicate the rows in the table. Under normal circumstances this wouldn't be a challenge, however, this isn't a normal circumstance. Normally 'duplicate rows' is defined as two or more rows containing the exact same values for all columns. In this case 'duplicate rows' is defined as two or more rows that have the exact same values, but are also within 20 seconds of each other. I wrote a script that is still running after 19.5 hours, this isn't acceptable, but I'm not sure how else to do it. Here's the script:</p> <pre><code>begin create table ##dupes (ID int) declare curOriginals cursor for select ID, AssociatedEntityID, AssociatedEntityType, [Timestamp] from tblTable declare @ID int declare @AssocEntity int declare @AssocType int declare @Timestamp datetime declare @Count int open curOriginals fetch next from curOriginals into @ID, @AssocEntity, @AssocType, @Timestamp while @@FETCH_STATUS = 0 begin select @Count = COUNT(*) from tblTable where AssociatedEntityID = @AssocEntity and AssociatedEntityType = @AssocType and [Timestamp] &gt;= DATEADD(ss, -20, @Timestamp) and [Timestamp] &lt;= DATEADD(ss, 20, @Timestamp) and ID &lt;&gt; @ID if (@Count &gt; 0) begin insert into ##dupes (ID) (select ID from tblHBMLog where AssociatedEntityID = @AssocEntity and AssociatedEntityType = @AssocType and [Timestamp] &gt;= DATEADD(ss, -20, @Timestamp) and [Timestamp] &lt;= DATEADD(ss, 20, @Timestamp) and ID &lt;&gt; @ID) print @ID end delete from tblHBMLog where ID = @ID or ID in (select ID from ##dupes) fetch next from curOriginals into @ID, @AssocEntity, @AssocType, @Timestamp end close curOriginals deallocate curOriginals select * from ##dupes drop table ##dupes end </code></pre> <p>Any help would be greatly appreciated.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload