How do I copy a table in MySQL?
Author: Deron Eriksson
Description: This tutorial describes how to copy a table in MySQL.
Tutorial created using: Windows XP || JDK 1.5.0_09 || Eclipse Web Tools Platform 1.5.1 || MySQL 5.0.27
Copying a table in MySQLW is quite easy. This task can be performed with a CREATE TABLE ... LIKE ... statement. This is illustrated in the following copy_employees.sql script.
USE testdatabase; DROP TABLE IF EXISTS employees; CREATE TABLE employees (id INT, first_name VARCHAR(20), last_name VARCHAR(30)); INSERT INTO employees (id, first_name, last_name) VALUES (1, 'John', 'Doe'); INSERT INTO employees (id, first_name, last_name) VALUES (2, 'Bob', 'Smith'); INSERT INTO employees (id, first_name, last_name) VALUES (3, 'Jane', 'Doe'); SELECT * FROM employees; DROP TABLE IF EXISTS employees_2; CREATE TABLE employees_2 LIKE employees; INSERT INTO employees_2 SELECT * FROM employees WHERE last_name = 'Doe'; SELECT * FROM employees_2;
This script creates an employees table and inserts three rows into the table. Then, it creates an employees_2 table, copying the structure of the employees table. Following that, it copies two rows of data from the employees table into the employees_2 table (the rows with the last_name 'Doe').
If we execute this script in Toad, we see the following result.
As you can see in the Set 2 Result Set display at the bottom of the screen capture, the employees_2 table has id, first_name, and last_name columns, and it contains two rows, as expected.