Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Copy table data from one schema to another scheman
L1 wrote:
> Doggy Daddy <spamhole_at_nospam.org> wrote in message news:<pan.2003.04.14.15.01.19.466406.15885_at_nospam.org>...
>
>>On Mon, 14 Apr 2003 12:34:52 -0700, L1 wrote: >> >> >>>Hello All, >>> >>>wonder if you know a simple way (sql, or plsql) to copy table data from >>>one schema to another schema for temporary achival purpose. >>> >>>thanks, >>>l1 >> >>Grant SELECT on the table to the archive schema, then connect to the >>archive schema and CREATE TABLE archivetable AS SELECT * FROM >>oldschema.tablename. >> >>You could also EXPORT the table, then IMPORT it into another schema (or >>just keep the export file for your archive), but you said you wanted a SQL >>or PL/SQL solution.
Well, you changed requirements from your original message! (we aren't mind readers you know.
What to do is learn to use SQL to write SQL. For example,
SELECT 'create table ' || table_name ||' as select * from oldschema.' ||
table_name || ';'
from all_tables
where table name in ('FIRSTTABLE', ... 'LASTTABLE') ;
using the SPOOL command and some SET options, you end up with a script to do what you want.
I leave it as an exercise to the student how this can be changed for the INSERT and UPDATES. (yes those are hearder but there is no new concept to learn!)
HTH
-- Ed Prochak running http://www.faqs.org/faqs/running-faq/ netiquette http://www.psg.com/emily.html -- "Two roads diverged in a wood and I I took the one less travelled by and that has made all the difference." robert frostReceived on Tue Apr 15 2003 - 12:08:15 CDT
![]() |
![]() |