How to Copy Row and Insert to Same Table in MySQL

MySQL allows you to copy data across tables and databases, using SELECT and INSERT statements. In some cases, you may need to copy row and insert to same table in MySQL. This is often required to create test data for quality checks, or duplication of data. In this article, we will learn how to do this.

How to Copy Row and Insert to Same Table in MySQL

Let us say you have MySQL table data(id, c1, c2, c3) where id column is primary key which auto increments itself.

Let us say you want to copy and insert to same table row with id=123, then here is the SQL query for it.

insert into data (c1, c2, c3)
select c1, c2, c3
from data
where id = 123

In the above query, MySQL will first select the rows specified in SELECT statement and then insert it into the table as per INSERT statement.

Please note, in this case, we have not included id column since it is automatically assigned. Also we have mentioned value for all the other columns in our table. The number of columns mentioned in SELECT clause must be same as that mentioned in INSERT statement. If you do not mention any column name in your insert query then it will be populated using the default value present in its definition.

If you want to explicitly assign value of id column in new row you can mention it in INSERT statement. Here is an example where we assign id=234 in new row.

insert into data (id, c1, c2, c3)
select 234, c1, c2, c3
from data
where id = 123

Alternatively, you can also create temporary table to temporarily store the values to be inserted and then use INSERT statement to insert these rows.

CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY

SELECT * FROM data WHERE id=123;
UPDATE temp_table SET id=0; /* Update other values at will. */

INSERT INTO data SELECT * FROM temp_table;
DROP TABLE temp_table;

In this article, we have learnt a couple of ways to copy row and insert them into the same table in MySQL.

Also read:
How to Run SQL Script in MySQL
How to Find Number of Columns in Table in MySQL
How to Search for Text in Every Field of Database in MySQL
How to Remove Leading & Trailing Whitespace from Column in MySQL
How to Remove Primary Key in MySQL