Re: Copy table data from one schema to another scheman

Hans Forbrich <forbrich_at_telusplanet.net> wrote in message news:<3E9C17F0.99E825F4_at_telusplanet.net>...
> L1 wrote:
>
> > 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?
> >
>
> Why write it yourself? Get tyhe system to do it for you - just write a SELECT statement against the catalog to
> generate the create table scripts.

The reason I wanna write script myself is because of the following requirement:

I need to back up the all tables in the schema A, as well as relevant rows of some tables from another schema B to a new schema called HISTORY. I need to add a fiscal_history column to each table in this HISTORY schema, the first time the script runs, i include the value "2003" in that column. then, the next year, we back up all the tables again, with the archive_year value being "2004"...

the way i am doing now is to use CREATE TABLE xxx ... as select ... from .... for each table in schema A, and pass the year 2003 or 2004 as parameter. It's very tedious because we have over 50 tables; plus i couldn't think of a way to insert that year value into every table every record in this achival schema.

Any thoughts?

Thanks alot,
l1 Received on Tue Apr 15 2003 - 14:57:05 CDT