Re: sql question

Michael Cressey wrote: > > Depending on how big this table is you might be better off > copying the data into a file and using an operating system > command to remove the duplicate rows. (sort -u on unix). > > Once this has been done you can copy the data back in to the > database. This is invariably quicker than letting the database > engine do the work. > > Make sure you've got a good backup first though! I've used the OS to IDENTIFY duplicate rows, not remove them. Due to an I/O error, I've had to run a verifydb on a 6.5Gb table (40 millions rows). It would not modify after due to a duplicate row. I copied out the table into a file, ran the Unix sort on the keyed field. Then wrote a small C program that would scan and check for 2 consecutive duplicate keys. Once identified, went back to the table, removed the row and remodified. All this within 12 hours. Andre Laframboise DBA National Library of Canada Ottawa, Ontario,