Copy a MySQL Table

MySQL provides different ways to copy a table depending on whether you need only the structure, only selected data, or both.

Copy structure and data

CREATE TABLE student2 AS SELECT * FROM student;

This copies selected columns and rows into a new table, but it does not automatically preserve every index or attribute from the source table.

Copy selected rows

CREATE TABLE student2 AS SELECT *
FROM student
WHERE class = 'Four';

Copy selected columns

CREATE TABLE student2 AS SELECT id, name, mark
FROM student;

IF NOT EXISTS

CREATE TABLE IF NOT EXISTS student5 AS SELECT *
FROM student
WHERE class = 'Four';

IF NOT EXISTS prevents an error when the destination name already exists; it does not verify that the existing structure matches the source.

Re-create a disposable copy

DROP TABLE IF EXISTS student5;
CREATE TABLE student5 AS SELECT * FROM student;

Preserve structure and then copy data

When keys and table attributes matter, first copy the structure with LIKE, then insert the rows:

CREATE TABLE student2 LIKE student;
INSERT INTO student2
SELECT * FROM student;

Copy only the structure

CREATE TABLE student2 LIKE student;

CREATE TABLE ... LIKE copies the table definition, including indexes, without copying rows.

Show the original CREATE statement

SHOW CREATE TABLE student;

PHP PDO example

<?php
require 'config.php';
$tableName = 'student';
// Use a trusted/whitelisted table name; identifiers cannot be bound like values.
$stmt = $dbo->query("SHOW CREATE TABLE `student`");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['Create Table'];
?>

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: