Re: Super Table

Or even better,

create view everybodys_stuff as
select 'schem1' schema, * from schem1.table1 union all
select 'schem2', * from schem2.table1
union all
select 'schem3', * from schem3.table1

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:37F56367.83C_at_yahoo.com...
> In2Home User wrote:
> >
> > I have the same table structure represented in many different schemas:
> > schema1.test, schema2.test, etc.
> >
> > Where test has columns, say: id, title, designer, status.
> >
> > Can I create a view that appears to be a single table representing all
the
> > data
> > in each test table in all the schemas? If so, how?
> >
> > What I'd like is a view that has columns: schema, id, title, designer,
> > status.
> >
> > So if schema1.test has 200 records and schema2.test has 400 records,
> > the view will have 600 records.
> >
> > If I cannot use a view, is there a better way to do this than using
triggers
> > to populate
> > two tables ... rather costly?
> >
> > Thanks,
> >
> > Chris
>
> (As an account that can read all the schemas issue)
>
> create view everybodys_stuff as
> select * from schem1.table1
> union all
> select * from schem2.table1
> union all
> select * from schem3.table1
>
> Cheers
> Connor
Received on Mon Oct 04 1999 - 19:47:21 CDT