Welcome readers, in this tutorial, we will learn how to create an exact copy of an existing table in the same or different database.
1. Introduction
Cloning or Copying a table is an important phenomenon that helps developers rescuing disaster recovery situations or to perform something without affecting the original table. To start with this tutorial, we are hoping that users at present have their preferred database installed on their machines. For easy usage, I am using MySQL on a Windows operating system. If someone needs to go through the MySQL installation, please watch this video.
2. SQL Copy Table Example
The following tutorial will illustrate the different approaches for performing the cloning operation.
2.1 Creating a Sample Table
The following script creates a table: sql_copy_table_tutorial.employee and adds some sample data to it.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | /* Sql to create database. */CREATE DATABASE IF NOT EXISTS sql_copy_table_tutorial;/* Using the newly created database. */USE sql_copy_table_tutorial;/* Creating an employee table and adding dummy data to it. */CREATE TABLE IF NOT EXISTS employee ( id INT NOT NULL, username VARCHAR(50), fullname VARCHAR(50),xx date_of_joining DATE, email_address VARCHAR(50), gender VARCHAR(50), PRIMARY KEY (id));INSERT INTO employee (id, username, fullname, date_of_joining, email_address, gender) VALUES (1, 'bpankettman0', 'Blaire Pankettman', '2016-09-16', 'bpankettman0@feedburner.com', 'Female');INSERT INTO employee (id, username, fullname, date_of_joining, email_address, gender) VALUES (2, 'rrubel1', 'Roxanne Rubel', '2018-01-18', 'rrubel1@feedburner.com', 'Female');INSERT INTO employee (id, username, fullname, date_of_joining, email_address, gender) VALUES (3, 'ktravis2', 'Kirsteni Travis', '2007-02-28', 'ktravis2@feedburner.com', 'Female');INSERT INTO employee (id, username, fullname, date_of_joining, email_address, gender) VALUES (4, 'cpunton3', 'Chadd Punton', '2002-07-03', 'cpunton3@feedburner.com', 'Male');INSERT INTO employee (id, username, fullname, date_of_joining, email_address, gender) VALUES (5, 'mworpole4', 'Micky Worpole', '2004-10-14', 'mworpole4@feedburner.com', 'Male');/* Display employee table data. */SELECT * FROM employee; |
If everything goes well, the employee table will be created.
2.2 Simple Copy Table Commands
Programmers can use the following different queries to create an exact copy of the table.
2.2.1 Creating a Table Based on Definitions
The following commands will create a table based on the original table definitions and populate the empty data with the data from the original table.
/* #1: Query to create a table based on the definitions of the original table. */ CREATE TABLE employee_bkp1 LIKE employee; /* Populating the empty table (employee_bkp1) with the data from the original table. */ INSERT employee_bkp1 SELECT * FROM employee;
If everything goes well, the employee_bkp1 table will be created.
2.2.2 Creating a Table without Column Definitions
The following commands will create a table without taking into consent any column definitions and directly inserting the data into the empty table.
/* #2: Query to create a table without taking into consent any column definitions and directly inserting the data into the table. */
CREATE TABLE employee_bkp2 SELECT * FROM
employee;
If everything goes well, the employee_bkp2 table will be created.
2.2.3 Creating a Table with Selected Columns
The following commands will create a table with selected columns and directly inserting the data into the empty table.
/* #3: Query to create a table with selected columns and directly inserting the data into the table. */
CREATE TABLE employee_bkp3 AS (SELECT id, username, fullname, email_address FROM
employee);
If everything goes well, the employee_bkp3 table will be created.
2.2.4 Creating a Table in Different Database
The following commands will create a table in a different database and populating the data in the empty table from the original table.
/* #4: Query to create a table in a different database and populating the data with the data from the original table. */
CREATE TABLE sql_copy_table_tutorial_bkp.employee_bkp AS (SELECT * FROM
employee);
If everything goes well, the employee_bkp table will be created in sql_copy_table_tutorial_bkp database. That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!
3. Conclusion
In this section, developers learned how to create an exact copy of the existing table. Developers can download the sample scripts in the Downloads section.
4. Download the SQL Script
This was an example of creating an exact copy of the existing table.
You can download the full source code of this example here: SQL Copy Table Example
