Need query to split table into 2 linked tables

[Guest]

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

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Here's how I'd do it:

1) Create an AutoNumber column in the very big table. Name that column
"OrigNumber."
2) Create table 1 w/ the ID column as an AutoNumber column and the
OrigNumber column as a Long integer data type.
3) Load table 1 w/ the OrigNumber and the other columns you want.

INSERT INTO table1 (OrigNumber, <other columns>)
SELECT OrigNumber, <other columns>
FROM VeryBigTable

4) Create table 2 w/ the OrigNumber column as a Long integer data type.
5) Load table 2 w/ the OrigNumber and other columns you want in table 2.

INSERT INTO table2 (OrigNumber, <other columns>)
SELECT OrigNumber, <other columns>
FROM VeryBigTable

6) Create a new column in table 2 named ID as a Long integer data type.

ALTER TABLE table2 ADD COLUMN ID Long;

7) Run the following query:

UPDATE table2
SET ID = (SELECT ID FROM table1 WHERE OrigNumber = table2.OrigNumber)

8) After that query successfully runs, run the following DDL statements,
in the SQL view of a query def, one at a time:

ALTER TABLE table1 DROP COLUMN OrigNumber;

ALTER TABLE table2 DROP COLUMN OrigNumber;

CREATE UNIQUE INDEX idxID ON table1 (ID);

ALTER TABLE table2 ADD CONSTRAINT FK_table2 FOREIGN KEY (ID)
REFERENCES table1 (ID)

Changes table names as required.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkw+yoechKqOuFEgEQK55ACg6Vw5gOdR88T59yL76skLMPifs3YAnjng
EQetOV9owa5lSN2jNgKXNYId
=w+TM
-----END PGP SIGNATURE-----

 

[John Vinson]

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.

I think I'd really recommend stepping back and reconsidering the very
structure of this table. 200 fields is about 170 too many for any
properly normalized table! One to one relationships are a "band-aid"
solution; what you probably should do is consider major surgery
instead, breaking this spreadsheet into two or more (probably
several!) properly normalized tables.

The table analyzer can give a worthwhile starting point for this
exercise; better is to sit down with pencil and paper, and identify
the "Entities" (real-life things, persons or events) represented by
this table, and create a single table for each entity.

John W. Vinson[MVP]

 

[Guest]

Yes, that would be the 'proper' solution. This data was generated from a
mainframe system and imported as a flat file (whose format I have no
influence on or control over). It is not structured at all and certainly is
as far frim normalized as it is possible to get. I have already spent 2
months writing various reports that use this table. Its only because these
reports are slow that I have considered splitting the table into two.
Unfortunately a complete rewrite is impossible especially since my deadline
is today.
David

 

[John Vinson]

Its only because these
reports are slow that I have considered splitting the table into two.

If your reports will be based on a query joining the two tables one to
one, they will be SLOWER than if you kept the single table. You'll be
adding the time to join the two tables to the time to retrieve the
fields and format the report!

Try instead putting indexes on any fields used for sorting or
searching, if the table does not now have such indexes.

John W. Vinson[MVP]

 

[John Spencer (MVP)]

Although splitting the table into two portions may allow more indices to be
applied. That would be a way of working around the limits on the number of
indices in any one table.

 

[John Vinson]

Although splitting the table into two portions may allow more indices to be
applied. That would be a way of working around the limits on the number of
indices in any one table.

Good point; and if any of the reports need only one of the tables,
those particular reports could be based on the smaller table.

John W. Vinson[MVP]