Re: Copy table data from one schema to another scheman

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Tue, 15 Apr 2003 17:08:15 GMT
Message-ID: <3E9C4076.7020903@adelphia.net>

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?
>
> Thanks,
> l1

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 frost

Received on Tue Apr 15 2003 - 12:08:15 CDT