O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

How to do it...

  1. 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required