Merging the Structure (not just the contents) of several small tables into one large table

Hi
I have 10 small tables of 3,4 or 5 fields and I want to merge them
into one table with 40 or so fields, they all share the same index.

eg merge table1 (f1,f2,f3) table2 (field4,field5,field6,field7)

into new table

table3 (f1,f2,f3,f4,f5,f6,f7)

is there any automatic way of doing this (i'm not so bothered if I
lose the data)


--
David Rayner
http://motivation-tips.co.uk/

On 6 Jun, 16:20, zzapper <zzap...@gmail.com> wrote:
> Hi
> I have 10 small tables of 3,4 or 5 fields and I want to merge them
> into one table with 40 or so fields, they all share the same index.
>
> eg merge table1 (f1,f2,f3) table2 (field4,field5,field6,field7)
>
> into new table
>
> table3 (f1,f2,f3,f4,f5,f6,f7)
>
> is there any automatic way of doing this (i'm not so bothered if I
> lose the data)
>
> --
> David Raynerhttp://motivation-tips.co.uk/

INSERT INTO SELECT ...
as documented here:
http://dev.mysql.com/doc/refman/5.0/...rt-select.html

> > Hi
> > I have 10 small tables of 3,4 or 5 fields and I want to merge them
> > into onetablewith 40 or so fields, they all share the same index.
>
> > eg merge table1 (f1,f2,f3) table2 (field4,field5,field6,field7)
>
> > into newtable
>
> > table3 (f1,f2,f3,f4,f5,f6,f7)
>
> > is there any automatic way of doing this (i'm not so bothered if I
> > lose the data)

>
> INSERT INTO SELECT ...
> as documented here:http://dev.mysql.com/doc/refman/5.0/...rt-select.html
Hi
I realise now what I failed to communicate, I want to find an
automatic way of creating the new table structure
by reading/describing the structure of the existing smaller
individual tables.

I have found
CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id))
ENGINE=MyISAM SELECT * FROM tbl_music_selected,SELECT *
FROM tbl_speakers_selected ;

But this only seems to allow one table to be selected and or blows up
on duplicate columns



Don't mind hacking it with a bit of Perl


--
zzapper
At last I've found a healthy energy drink
http://www.drinkact.com/thesuccess/

On Jul 31, 8:03 pm, zzapper <zzap...@gmail.com> wrote:
> > > Hi
> > > I have 10 small tables of 3,4 or 5 fields and I want to merge them
> > > into onetablewith 40 or so fields, they all share the same index.
>
> > > eg merge table1 (f1,f2,f3) table2 (field4,field5,field6,field7)
>
> > > into newtable
>
> > > table3 (f1,f2,f3,f4,f5,f6,f7)
>
> > > is there any automatic way of doing this (i'm not so bothered if I
> > > lose the data)
>
> > INSERT INTO SELECT ...
> > as documented here:http://dev.mysql.com/doc/refman/5.0/...rt-select.html
>
> Hi
> I realise now what I failed to communicate, I want to find an
> automatic way of creating the new table structure
> by reading/describing the structure of the existing smaller
> individual tables.

Doh!
1)Using a GUI such as SQLYOG select desired existing tables
2) Save as SQL
3) Using VIM (or any regex editor) edit out the duplicate
creates,index,timestamp fields etc
4) Delete (bulk) insert statements
5) add create new table
6) execute script
takes about 20 minutes

--
zzapper
At last I've found a healthy energy drink

http://www.drinkact.com/thesuccess/