- Use the INSERT INTO SELECT statement:
mysql> CREATE TABLE employees_clone LIKE employees;mysql> INSERT INTO employees_clone SELECT * FROM employees;
Note that if there are any generated columns, the above statement would not work. In that case, you should give full insert statement excluding the generated columns.
mysql> INSERT INTO employees_clone SELECT * FROM employees;ERROR 3105 (HY000): The value specified for generated column 'hire_date_year' in table 'employees_clone' is not allowed.mysql> INSERT INTO employees_clone(emp_no, birth_date, first_name, last_name, gender, hire_date) SELECT emp_no, birth_date, first_name, last_name, gender, hire_date FROM employees;Query OK, 300024 rows affected (3.21 sec)Records: 300024 ...
