Re: Copy table data from one schema to another scheman

From: Doggy Daddy <spamhole_at_nospam.org>
Date: Tue, 15 Apr 2003 15:20:44 -0700
Message-ID: <pan.2003.04.15.15.20.44.399269.16741@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. Received on Tue Apr 15 2003 - 17:20:44 CDT