MySQL Question

jazappi

I have a table that contains about 15 fields I would like to migrate to a diffrent table, what the have in common is a "company_id", so with that being said, I need to get the data and structure from part of one table, and insert it into the other table data and structure where company_id is like company_id.

Any suggestions in an easy way to do this?

Thanks for the input and help in advance.

Sxooter

If your database supports select into:

select field1, field2, field3, fieldn into newtable from oldtable

If not then maybe create table as

create table tablename as select field1, field2, field3, fieldn from oldtable

This kind of thing tends to be a bit outside the realm of the SQL spec, so each implementation can be different.

Drakla

Mysql uses the insert / select syntax, so you can do what Sxooter says with

INSERT INTO mytable SELECT fielda, fieldb, fieldc FROM someothertable

You can even stick conditions on the select. Just take a look at the mysql manual for insert syntax if you get any hassle

jazappi

Do I need to go ahead and create the fields in the NewTable or win I insert will MySQL create the fields?

Sxooter

you create the table first. insert into / select is the ansi standard by the way, I just wasn't sure if mysql supported it since it's technically a subquery.