MySql : How to Copy records from one table to another

Mysql is one of the most used databases in the world. It is proved with the number of websites using MySql as their database system, and today, the most popular Content Management Systems including Worpdress, Joomla and many other Blogging Platforms are also using MySql. One reason for the popularity of Mysql is it is Free and Open Source. 

So if you are also working with MySql databases, this blog post will be there to help you someday.

Sometimes you may face instances where you want to copy an entire record of a database table to another table

What I am going to show you today is the way of copying a row of a database table to another using a single Mysql query. This is very useful in cases where you want to take a backup record of a row. If the application you are developing has an option to view the update history of a certain record, you can simply do it with this query by copying the whole row to another table.

Note : For this query to work, both the tables must have same fields. 

Example : 
Here We are considering two tables, 'JOBS' and 'JOBS_BACKUP'. Note that both the tables have the same fields and same properties. In this case, the user is updating the Job which has the ID : 8, and I wanted to take a snapshot of the Job details for the job id 8, as how it was before editing. So later if needed, I have the ability to compare the difference between the two versions, and also I will have all the information I need to check which user has made the update, and when the update was done etc. 

INSERT INTO jobs_backup (
job_id,   
cat_id,
user_id,
start_date,
description,
job_location,
date_created,
last_update,
last_editby,
verified
) SELECT
job_id,
cat_id,
user_id,
start_date,
description,
job_location,
date_created,
last_update,
last_editby,
verified
FROM
jobs 
WHERE
job_id = 8;

As a general query, We can rewrite the above again as :

INSERT INTO table_02 (
field_1,   
field_2,
field_3
) SELECT
field_1,
field_2,
field_3
FROM
table_01 
WHERE
field_1 = 'your value';

I hope this would be helpful to someone who is looking for a way to copy database records in between two tables. Please don't forget to share your comments on this post if this was useful to you :