sql

SQL Copy Table Example

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.

SQL Copy Table - sample table
Fig. 1: Creating a sample table

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.

SQL Copy Table - Table based on Definitions
Fig. 2: Creating a Table based on Definitions

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.

SQL Copy Table - Table without Column Definitions
Fig. 3: Creating a Table without Column Definitions

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.

SQL Copy Table - Table with Selected Column
Fig. 4: Creating a Table with Selected Columns

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.

Download
You can download the full source code of this example here: SQL Copy Table Example

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button