Re: merge serval table into a table?

I would create the table structure first (this way you can specify a primary key) and then copy the rows from the other tables into the new one:

insert into new_table
select * from a1
union all
select * from a2
union all
select * from a3
... and so on.

If you dont have enough space, you can "merge" and drop the tables one by one.

wy_at_fudan.edu wrote:

> I have created serval tables which have the same structure.
> eg.
> create table A1 /*contain less then 100 records*/
> (code char(2),
> value char(10),
> primary key code);
> and Table A2,A3,A4 is the same ,record the different information.
>
> some one advised me that I should merge them into one table like below:
> create table A
> (id char(2), /*it should be A1 ..A4*/
> code char(2),
> value char(10),
> primary key(id,code));
>
> I want to know if I have a sql,the sql need link all of them:
>
> select * from b,a1,a2,a3,a4
> where
> b.code1 = a1.code
> b.code2 = a2.code
> b.code3 = a3.code
> b.code4 = a4.code
>
> or
>
> select * from b,a a1,a a2,a a3,a a4
> where
> b.code1 = a1.code and a1.id='A1'
> b.code2 = a2.code and a1.id='A2'
> b.code3 = a3.code and a1.id='A3'
> b.code4 = a4.code and a1.id='A4'
>
> which one run in higher performance!
>
> Any suggestion are apreciated!
>
> Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
To reply to me directly, please mail to: bmlam at online dot de