How do I copy an existing table and give it a primary key?

[BrimReap]

I have a table with out a primary key. I want to copy this table's data/structure into a new table with a primary key (with auto increment attribute). What code can I add on to this to acheive this? Will it assign id's to the current data?

select *
into NEW_TABLE
from OLD_TABLE

 

[sleipnir214]

I would create the new table, then issue an insert...select query:

http://www.mysql.com/doc/en/INSERT_SELECT.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

there are a couple of ways to do it

note that

SELECT INTO newtable FROM oldtable

will not work in mysql (that syntax is microsoft)


the way i would do it is simply alter the existing table and add the primary key to it


alter oldtable
add id integer not null primary key auto_increment

voila

rudy

http://r937.com/

SQL Consulting