Need to split table into two linked tables

[ZABADAK]

I have imported a table which is very big (over 200 columns). I want to split this table into two linked (in one-to-one relationship) by an autonumber ID. Is there an efficient way to do this? I would assume I can't write out table 2 until table 1 has been completely written and the autonumber IDs assigned. Or is there a way to do it in a single query?

Table 1
ID autonumber
Data

Table 2
ID links to ID in table 1
Data

 

[rac2]

Creating the tables will be fairly simple. Table 1 can be the master table with the autonumber column. Table 2 will have a column, known as a foreign key, which contains the ID number from the master table, but this cannot be an autonumber column. Because it is a foreign key the values must match values in the master table. In the master table, the ID column is known as the primary key. A foreign key in one table references a primary key in some other table.

Table 2 could have an autonumber column, that will be its primary key. But the value will not refer to Table 1.

Every table should have a primary key column and typically this is an autonumber column. It has various uses such as handling a specific row in an UPDATE; or as above, in JOINing two tables.

You will need to devise a method for INSERTING rows into Table 2 which inserts the foreign key value for the corresponding row INSERTed into Table 1. This is not trivial.


As long as the database engine can handle 200 columns, why not simplify life and keep the table intact. For human consumption, create VIEWs which display a SELECTed assortment of a few columns for particular purposes. This will be easier.

 

[traingamer]

Just use the same key as the primary key in both tables. In the second table it should just be a long integer (number) NOT autonumber. Make sure the tables are related one-to-one in the relationships area of the database.
Move lesser used fields to the second table (as I assume you are doing this for speed purposes).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ZABADAK]

Thanks for the responses. I already know everything that has been said. But how do I get the data into the second table with the key being the autonumber of the first table?

 

[PHV]

Doesn't have your original "huge" table a PK ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244