Importing data from old table to new table

[cyberdyne]

hi all ,
my table structure has changed. I have added some constraints and new fields.
Now I want the previous tables data to be imported to this new table. how can i do that ?
i am using oracle 8.0 for NT. From:
Apoorva Gala
You can mail me at apoo1972@rediffmail.com

 

[carp]

You won't be able to import directly into the new table. Instead, I think you will have to import the old table, then do a SELECT to move the old data into the new table:

INSERT INTO new_table(columns_that_were_in_the_old_table)
SELECT(columns_to_be_moved_to_new_table) FROM old_table;

 

[kindus]

Yes, carp's approach is correct. Only changes you may require is if any or some of the new columns are not null, those need to specified and some dummy value may be put in the select as well. Code will look like

INSERT INTO new_table(columns_that_were_in_the_old_table, not_null_new_columns)
SELECT(columns_to_be_moved_to_new_table,'dummy_numeric_or_char_etc_for_the_notnull_new_colums') FROM old_table;

I think now it should be okey

Hope this helps!

 

[cyberdyne]

Thanks Carp and kindus
Is there any other way because my project contain 42 tables and each table contains around 500 recordsets. From:
Apoorva Gala
You can mail me at apoo1972@rediffmail.com

 

[sem]

You may automate the last stage by importing tables to another schema and then building insert scripts automatically.

set feedback off
set serverout on size 32000

spool 'cmd'
declare
fields varchar2(2000);
STMT varchar2(32000);
temp varchar2(60) := 'TEMP'; -- temporary schema
perm varchar2(60) := 'PERM'; -- existing schema
begin
for ts in (select t.table_name from all_tables t, all_tables c
where t.owner = temp and c.owner= perm and c.table_name = t.table_name)
loop
fields := null;
for cs in (select t.column_name
from all_tab_columns t, all_tab_columns c
where t.owner = temp and t.table_name = ts.table_name
and c.owner = perm and c.table_name = ts.table_name
and c.column_name = t.column_name)
loop
if fields is null then
fields := cs.column_name;
else
fields := fields||','||cs.column_name;
end if;
end loop;
stmt := 'insert into '|| perm||'.' || ts.table_name
|| ' ('||fields || ') (select '|| fields
|| ' from '||temp||'.' || ts.table_name||')';
dbms_output.put_line(stmt);
dbms_output.put_line('/');
end loop;
end;
/
spool off
@cmd

Of course you may also check for column types in the second cursor.