Copy Data Between Existing Tables with INSERT ... SELECT

INSERT ... SELECT copies rows returned by a query into an existing destination table. This is useful when the target table already exists and you want to copy all rows, selected rows or selected columns.

Example tables

student  : 35 records
student2 : 0 records

The examples assume compatible destination column types and a primary key on id.

Copy all rows with INSERT ... SELECT

INSERT INTO student2
SELECT *
FROM student;

For production code, explicit column lists are safer because they make the mapping clear if a table definition changes.

Copy selected rows

INSERT INTO student2 (id, name, class, mark, gender)
SELECT id, name, class, mark, gender
FROM student
WHERE class = 'Four';

Copy selected columns

INSERT INTO student2 (id, name, class, mark)
SELECT id, name, class, mark
FROM student;

If an omitted destination column permits NULL or has a default, MySQL supplies the appropriate value. Otherwise the insert can fail.

What happens on duplicate keys?

#1062 - Duplicate entry '3' for key 'PRIMARY'

A normal INSERT fails when an incoming row conflicts with a destination PRIMARY KEY or UNIQUE key. Decide explicitly whether a conflict should be rejected, updated or replaced.

REPLACE ... SELECT

REPLACE INTO student2 (id, name, class, mark, gender)
SELECT id, name, class, mark, gender
FROM student
WHERE class = 'Four';

INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO student2 (id, name, class, mark, gender)
SELECT id, name, class, mark, gender
FROM student
WHERE id = 3
ON DUPLICATE KEY UPDATE mark = 5;

This inserts the row when no conflict exists and updates mark when the incoming key already exists.

Use values from the SELECT source

INSERT INTO student2 (id, name, class, mark, gender)
SELECT src.id, src.name, src.class, src.mark, src.gender
FROM (
    SELECT id, name, class, mark, gender
    FROM student
    WHERE class = 'Four'
) AS src
ON DUPLICATE KEY UPDATE
    mark = 200,
    gender = src.gender;

The derived-table form provides an unambiguous name for values coming from the source query and avoids relying on the deprecated VALUES(column) form.

Transfer when table layouts differ

INSERT INTO plus2_inv_stock (p_id, qty, price_sell)
SELECT p_id, 0, 0
FROM plus2_inv_products;

The source and destination do not need identical layouts; the selected expressions only need to match the destination column list in number and compatible data type.

Clear the destination without dropping its structure

TRUNCATE TABLE student2;

TRUNCATE TABLE removes all rows, so use it only when deleting the complete destination dataset is intentional.

Related tutorials

Copy data into a new table · ON DUPLICATE KEY UPDATE · WHERE filtering · Rename tables · Export records as CSV

Download the student table SQL dump

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: