Home » SQL

Clone data between tables in SQL

In this article, we are going to learn about the cloning of data between two different tables in SQL.
Submitted by Manu Jemini, on March 05, 2018

A Table can hold a lot of data which is very important in some cases. This data can be required by other table or we can also want a totally new table.

Now imagine if you want to have a new table with the same records, in that situation cloning the entire data from a table to another table might be useful.

There are other situations where you want to have an archive of the data of a table then cloning the data from a table into another table and then deleting the data from that table will be very useful.

Cloning from a table is very simple and easy. Also, you can add additional fields which could be helpful if you want to make a new table which will definitely manage the data.

To clone data from a table all we need to do is to make an insert query which will take the data from the select query. Data should be serialized and compatible. You can do something like this:

INSERT INTO A( id, name ) select id, name from B 

Dummy table:

Syntax 1:

CREATE TABLE 'includehelp'.'employee' (
  'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  'name' VARCHAR(45) NOT NULL,
  'age' INTEGER UNSIGNED NOT NULL,
  'address' VARCHAR(45) NOT NULL,
  'salary' INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY ('id')
)
ENGINE = InnoDB;

Syntax 2:

CREATE TABLE 'includehelp'.'employee' (
  'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  'name' VARCHAR(45) NOT NULL,
  'age' INTEGER UNSIGNED NOT NULL,
  'address' VARCHAR(45) NOT NULL,
  'salary' INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY ('id')
)
ENGINE = InnoDB;

Example 1:

INSERT INTO CLONE_EMPLOYEE (id,name,age,address,salary)
SELECT id,name,age,address,salary FROM EMPLOYEE;

Example 2:

SELECT * FROM 'includehelp'.'clone_employee'