AVAJAVA Web Tutorials

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.

copy_employees.sql

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.