Re: Copy table data from one schema to another scheman

Doggy Daddy <spamhole_at_nospam.org> wrote in message news:<pan.2003.04.17.20.22.49.958965.18158_at_nospam.org>...
> On Thu, 17 Apr 2003 15:17:21 -0700, L1 wrote:
>
> > 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.
>
> You can do it, but you probably don't want to use the same technique that
> you used to copy the base tables. If you just SELECT from the existing
> views, you're actually copying the rows, not copying the view.
>
> To copy the view, you'd want to use CREATE VIEW statements in the archive
> schema that reference your archive tables. As with the table copy
> operation, you could either hand-write a script to create appropriate
> archive views, or you could inspect the data dictionary to get the view
> definitions and then either generate a SQL script with the appropriate
> CREATE VIEW statements or use dynamic SQL as before.

Since the Alter View xxx Add xxx. will only allow adding constraints, but not columns, creating the view manually is the only way i can think of to accomplish the task described above which will be very tedious.

Any other suggestions?

l1 Received on Mon Apr 21 2003 - 12:55:15 CDT