I have a table with 750,000 records. The table is detailed medical claims data. For example, there may be 14 records (or pick your favorite positive integer greater than zero, it doesn't matter) that represent one single claim. Each of these 14 records shows the claim number.
What I want to do is create a second table which is more or less a summary of this table. I want each record in the summary table to be a unique claim number. This table needs to be a table in and of itself, not just a summary report of the existing table or a table occurrence of the existing detailed table. For now, all I want to do is have one single element in this summary table: the claim number.
The way I'm doing now is performing a script that sorts all the records by claim number, goes to the first record, stores the claim number to a local variable, creates a new record in the summary table and puts the local variable there. It then goes back to the detailed table, goes the next record and compares that record's claim number to the one stored in the variable. If they're identical, go to the next record and make the comparison of the variable with this new record. If they're different, store the new claim number in the variable, go to the summary table, create a new record and put the variable there. Repeat this process until the end of the detailed file.
It's logical enough and it does in fact work. However, I had the script running all night and it still didn't finish. In fact, when it get about half way through it slows to a crawl.
Is there a better way accomplish what I'm trying to do?