Search code examples
mysqldatabasecloning

Cloning a table MYSQL and adding a unique ID?

The code I have been using to clone a table is as follows:

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

However, there is a problem that in the new table I want a unique, auto incrementing id column.

But I run into the column count doesn't match value count at row 1 error.

I know that you can use the DEFAULT keyword, or leave the id out and it will auto increment but I am unsure as how the query should be structured?

Solution

  • After you added the PK auto increment column (assuming its the first column) try this:

    INSERT INTO new_table 
SELECT NULL,[all column names from original_table] FROM original_table;