[francislang]

I'm trying to normalise a badly designed database. I have created new tables that split one huge table up, and I think it is now in 3NF.

My problem is that once i've created the new tables, How do I get the data out of the huge table into the smaller ones. Are there SQL commands to do this? I'm using Oracle 8i.

Any help would be greatly appreciated.

Many thanks,

Francis.

[alligatorsql.com]

Hello,

there are many ways to do this :
For my explanation I use BIG as the name for the bigtable and SMALL1, SMALL2 for the new designed tables :

1)
Create a SQL statement that give you back the dateiled result of table SMALL1 and SMALL2

After that use
INSERT INTO SMALL1 (field1, field2...)
SELECT field1, field2 ... FROM big WHERE ....

The Select part is your designed Select statement .. to this with both queries ... OK ?

2) Export the datas with SQLLOADER and import them ... (see SQLLOADER documentation)

3) Write a PL/SQL procedure to select the datas from the big table and put them into the new one ...

If you have problems writing the procedure - just let me know - I will help you ...

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

*** did you ever use Oracle8i, MySQL, Interbase and Birdstep RDM Server databases at the same time ? No ? Use AlligatorSQL ***

[francislang]

Thanks for you're prompt response.

I had thought of writing it as;

CREATE TABLE SMALL1(Column3, Column2, Column6)
AS
SELECT Column3, Column2, Column6
FROM BIGTABLE;

and do the same for all the other small tables. Then drop the big table.

Would this work?

Many thanks again.

[alligatorsql.com]

Hello,

thats perfect and very quick too ... I though, that you have already created the tables ...

Do it in this way

By the way ... are you interested in a new Oracle tool ... just send me an E-Mail to webmaster@alligatorsql.com and I give you some information about AlligatorSQL.

Thanks

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com