>I am trying to normalize a large, hard to manage table into several smaller ones. For example the larger table contains, among other fields, a custodian field. I want to replace this with a custodian id field and link the custodian table to the large table through the custodian id. My problem is, using the select distinct SQL statement seems to work fine but I can't figure out how to insert the distinct values into an existing table. I keep overwriting the table.
>
>Now
>Large table:
>custodian: fred
> mike
> john
>
>Proposed
>Large table:
>custodian id 1
> 2
> 3
>
>Custodian table:
>Custodian id 1 Custodian fred
> 2 mike
> 3 john
>
>thanks for any help

Michael,

This is really a pretty simple problem. To keep things that way, I'll give you the programatic method (one of them anyway).

First, create your new tables with the appropriate columns and data types. I'm betting you've already done this.

Second you need a way to auto increment the id column as you add rows. (More on this in a minute)

Third, fire your select from the original table to get your rows into a cursor
(example: SELECT DISTINCT custodian FROM large_table INTO CURSOR curCustodians

Fourth, programatically move through the cursor you just created. The easy way is with a SCAN...ENDSCAN

Fifth, as you scan the cursor;
(example: INSERT INTO Custodian_Table (custodian_id, custodian) ;
VALUES(GetNewID("Custodian_Table", "Custodian_ID"), curCustodians.custodian) )

Fifth (sub note), So, where does the lnNewCustodianID value come from. You could increment the value as a counter in the scan. However, what I would do is create a small procedure file (You won't need it after the initial load though the code for this method can come in handy) I would write it like this;

PROCEDURE GetNewID
LPARAMETERS lcTableName, lcColumnName)
SELECT MAX(&lcColumnName) AS MAX_ID FROM &lcTableName INTO CURSOR curID
RETURN (curID.max_ID + 1)
ENDPROC

When this completes you will have a table of custodians with unique incremented custodian ID values. I would put a unique primary key index on the column before this for a couple of reasons. One to make sure that the id values are discreet, and so that this process will error if any violation occurs.

You can use a similar methodology to write the new version of the large table to populate your data.

Like I said, this is neither the most elegant way of doing this, nor is it necessarily the best way. It is an easy way that you can implement quickly.

Feel free to email me if I can help in any other way.

Regards,

Jason Tryon