Re: Copy table data from one schema to another scheman

Doggy Daddy <spamhole_at_nospam.org> wrote in message news:<pan.2003.04.15.15.20.44.399269.16741_at_nospam.org>...
> On Mon, 14 Apr 2003 20:02:01 -0700, 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.
> >
> >
> > So if I have 40 tables in Schema A, and wanna copy all 40 to schema B,
> > does that mean i have to write that CREATE TABLE script for each one of
> > those 40 tables and bundle them in a procedure? Is there any other easy
> > ways?
>
> That's pretty much what you're faced with. If you have a lot of these
> tables, or you anticipate doing this frequently or for many schemas, then
> it might be worthwhile to write some PL/SQL to loop through the data
> dictionary and do the copies via dynamic SQL rather than hand-coding all
> the statements like a monk in a scriptorium.
>
> That might also be an argument for abandoning the SQL approach and using
> export/import.

Thanks so much for everyone's help. I finally did it.

Another question I have is if this could be done for VIEW as well. I want to do the exact same thing for VIEW, copy views from schema A to schema B and then add that extra Archived_year column to the newly-created views in schema B.

l1 Received on Thu Apr 17 2003 - 17:17:21 CDT