Re: Copy table data from one schema to another scheman

L1 wrote:

> 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?
>

Yes. And they are the same thoughts as before, also independently detailed by Ed Prochak in his reply.

• Use SQL*Plus
• Write a SELECT against the data dictionary.
• Have the SELECT "write" the commands you need to a file
• Run the resulting file.

A simple example - not verified against the data dictionary (I leave that to you):

SELECT 'Create table ' || table_name || ' AS Select * from OLD_SCHEMA.' || table_name || ' ;'   FROM ALL_TABLES
 WHERE TBALE_NAME in ("list of tables")

You could also change this to add a new-line character, a {ALTER table_name ADD FISCAL_HISTORY NUMBER (4,0)} and possibly additional lines such as {UPDATE table_name SET FISCAL_HISTORY = &&1 WHERE FISCAL_HISTORY IS NULL} or other appropriate variant. Received on Tue Apr 15 2003 - 15:20:20 CDT