create ... select fails

[Gaspar Bakos]

Hi,

I am trying to making a copy of a table called "Sections" into a new
table (just to be created) called "CamCfgSec". This "CamCfgSec" has
different column names (but represent the same data, and in the same
column order).

I tried to achieve this with the
	create table ... select
syntax, but I failed. Maybe you have an enlightening idea.

Here are my trials:

-------
1.
drop table if exists CamCfgSec;
CREATE TABLE CamCfgSec select * from Sections;

Works, but _column names_ are copied verbatim from "Sections" table, and
they are not what I want,

-------
2.
I try to create the CamCfgSec with the favoured column names, and then
insert the data:

drop table if exists CamCfgSec;
CREATE TABLE CamCfgSec (
  CMCSECid int(1) NOT NULL default '0',
  CMCSECname char(30) NOT NULL default '',
  PRIMARY KEY  (CMCSECid)
) TYPE=MyISAM select * from Sections;

>>> ERROR 1062 at line 2: Duplicate entry '0' for key 1
Why?
-------
3. Given the caveats mentioned in the DuBois book p. 150:

CREATE TABLE CamCfgSec (
  CMCSECid int(1) NOT NULL default '0',
  CMCSECname char(30) NOT NULL default '',
  PRIMARY KEY  (CMCSECid)
) TYPE=MyISAM select SectionID as CMCSECid, SectionName as CMCSECname from Sections;

>>> ERROR 1060 at line 2: Duplicate column name 'CMCSECid'
Why?

------
4. Of course, this works:

drop table if exists CamCfgSec;
CREATE TABLE CamCfgSec (
  CMCSECid int(1) NOT NULL default '0',
  CMCSECname char(30) NOT NULL default '',
  PRIMARY KEY  (CMCSECid)
) TYPE=MyISAM;
insert into CamCfgSec select * from Sections;

I am sure that I am screwing up something with the syntax.
Any idea would be most welcome.

Cheers,
Gaspar