Table of contents

Introduction

Clone Table

2.1.

Simple Cloning

2.2.

Shallow cloning

2.3.

Deep cloning

Frequently asked questions

Key takeaways

Last Updated: Mar 27, 2024

Clone Table

Author Ankit Kumar

Introduction

Hola ninjas! Let us continue with our topic.

In this article, we will be learning how to Clone tables.

First, we will learn what a clone table is and what it can do. Then we will know how to clone a table and its different types. Subsequently, we will learn about the commands. Then we will learn about the syntax of Cone table commands accordingly and see how to use them in a real-time environment.

We will also see some suitable examples of commands, making it easier to use them.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Clone Table

There may come a moment in database operations when you need to clone or replicate an existing table to a new table owing to column and attribute similarities, either to execute a test without impacting the original table or for other personal reasons.

Source: https://giphy.com/explore/clone

So, if we just encountered a circumstance in which we needed to develop a new set of tables for the new functionality we're implementing. This table contains a lot of columns and is highly comparable to another table that handles a similar collection of data for a particular feature.

Because the existing table and the new table are so similar, I decided to clone the old table to make the new table. It's simple to perform with SQL since you can execute several procedures to meet your cloning needs. This tutorial will teach you how to replicate and clone existing SQL tables.

Types of cloning :

Simple cloning
Shallow cloning
Deep cloning

Simple Cloning

The first approach is called Simple Cloning, and it creates a table from another table without taking any column properties or indexes into consideration.

So, if I have a table called employee, I can simply build a table called employees without worrying about the field characteristics and indexes in the employee table.

Code:

create table employeesA select * from employee;

Shallow cloning

Shallow cloning is often used to produce a replica of an existing table's data structure and column characteristics without copying the contents. This will only result in creating an empty table based on the structure of the original table.

Code:

create table employeesA like employee;

employeeA: New table

employee: Existing table

Deep cloning

Deep cloning differs from Simple Cloning and is comparable to Shallow Cloning in that, as the name indicates, it generates a deep copy of the original table.

This indicates that the new table inherits all of the previous table's column and index characteristics. This is quite beneficial if you want to keep the existing table's indexes and characteristics.

To do this, we'll need to construct an empty table based on the original database's structure and characteristics, then pick data from the old table and put it into the new table.

Code:

create table employeeA like employee;

Insert into employeeA select * from employee;

Frequently asked questions

When I clone a table, are the role's privileges retained?
The roles are not transferred to the table cloned when you clone a table. In the cloned table, only the child objects of the original database (i.e., schema, table) would preserve their rights.
Can I make a copy of a table that is not a view?
When you copy and paste a table, a view of the original table is created by default.
How can I clone tables across schemas?
1) To relocate the table to the target schema, use the ALTER TABLE... RENAME command and argument.
OR
2) To clone the table in the target schema, use the CREATE TABLE... CLONE command and argument.
Create a clone of an existing table without using the create command.

select * into <new table> from <existing table> where 1=2

select top 0 * into <new table> from <existing table>

Do we pay for storage twice or once since zero-copy cloning creates a replica of the table?
Because all data is shared from the source version, there is no additional storage cost for cloning. Any altered data blocks local to the cloned copy would be the sole additional expense.

Key takeaways

This article has successfully taught you how to Clone tables.

We also saw some of the queries that will help us use the database. For example, we cloned a table employeesA. We learned to clone an existing table that is not of our use. We did it with the help of the command. We also saw different types of Cloning such as Simple cloning, Shallow Cloning, and Deep Cloning with the use of their respective examples.
Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job. Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation.