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:

SQL Clone data between tables

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;
SQL Clone data between tables Output 1

Example 2:

SELECT * FROM 'includehelp'.'clone_employee'
SQL Clone data between tables Output 2






Was this page helpful? Please share with your friends...

Are you a blogger? Join our Blogging forum.



Comments and Discussions





© https://www.includehelp.com (2015-2018), Some rights reserved.




close Like other websites, this site uses cookies to deliver relevant ads based on your interest, by using our website, you acknowledge that you have read our privacy policy.