create table from select then alter table by adding an auto increment column in mysql

as briefly explained in subject, I need to create a table by selecting existing value. The thing I would like to achieve is to have another column with auto incremented value.

This is what I already tried:

CREATE TEMPORARY TABLE temp_tb (    
    `row_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `stm_id` bigint(20) NOT NULL,
    descr varchar(20) NOT NULL,
    PRIMARY KEY (row_id)
);

Then开发者_Go百科 after with a select:

INSERT INTO temp_tb (
  select stm_id,descr from tb_export
)

I was expecting to have the row_id column prefilled at insert time, but I just got sql syntax error telling me that column count doesn't match value count.

Do you know if this is possible to achieve ?

thanks

---

you should provide the names of the columns you are inserting into your temp_tb:

INSERT INTO temp_tb (stm_id, descr) (
  select stm_id,descr from tb_export
)

---

If I am not mistaken, the syntax error has to do with your insert syntax. You have a temp_tb that has 3 fields, all not null. You are inserting 2 fields into that table with your insert statement. The MySQL ref lists the syntax for insert using select as:

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;