>>>What is the best method to identify duplicate records in a table?
>>>In this case there is approx 1.5 million records and i need to look at
>>>each field.
>>>
>>>your help will be appreciated.
>>
>>I dont know if this is the best way, but I programmatically check my tables for duplicate records (meaning all fields the same in more than 1 record) with SELECT DISTINCT * FROM MYTABLE into MYARRAY and compare RECCOUNT() OF original table to _TALLY. If _TALLY is less then I ZAP original table and APPEND FROM MYARRAY. HTH
>
>Could you try to Select ~1.5 million records to array?
Well my above syntax needs more coffee ;)
SELECT DISTINCT * FROM handle2 INTO ARRAY checkdup
handle2 has 1.3 million records with no duplicates. I ran this on my database server(dual 200Mz pentium with 132 MB of memory) and got 1307962 records selected in 1387.2 minutes. (and 3 users screaming the network locked up :))
Yes, this code is a resource hog... thats why I said I didnt know if it was the best way. I use this in a UDF I've setup as a maintenance process run directly on the server for my data warehouse, and I typically only run it overnight. With larger tables (like 2mil +) I sometimes get SQL memory error with it.
How would you handle this type of thing Edward?
Roxanne M. Seibert
Independent Consultant, VFP MCP
Code Monkey Like Fritos