-2

I have identified Millions of duplicated rows on a table I have inherited by using this query:

SELECT COUNT(*) AS NumRecords, AccessID, LEFT(SQLTEXT, 5000)
FROM Table
WHERE AccessID=5012
GROUP BY AccessID, LEFT(SQLTEXT, 5000)
HAVING COUNT(*)>1;

enter image description here

The only index I can use on the table is the AccessRequestID field - the SQLText field is VARCHAR(MAX) and there are over 100 million records here and since there is a varchar(MAX) column the table is HUGE and takes FOREVER to do anything with. How can I turn that Select statement into a delete to remove the duplicated records? I was trying to figure out how the write a CTE using Partition Rownum, but I'm not confident in it. My Idea would be to have it in a loop that starts with AccessID 1 and then increments by one until the end of the table (There are only 5012 unique accessIDs) Since I would be filtering the where by the NC Index it will hopefully be faster.

2
  • It gets me on the right track, the main problem is having hundreds of millions of records + VarcharMAX columns and how to effectively get rid of the duplication.. Thank you. Aug 7, 2021 at 18:15

1 Answer 1

2

maybe you can try to insert the valid rows into a new table and then replace the old table with the new one.

3
  • I would not really be opposed to this, but same question - Looking at the data above, how would I script one insert from each "set" that I want to keep? Aug 7, 2021 at 17:41
  • 1
    Use the SQL INSERT INTO SELECT Statement and use a SELECT DISTINCT statement. Make sure that no SQLTEXT is longer than 5000, otherwise you might create duplicates by truncating the text. Using a loop is certainly the slowest option. Aug 7, 2021 at 18:08
  • Thank you - this is the way I went with it.... Aug 8, 2021 at 0:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.