How to Duplicate or Clone SQL Tables – Methods

  • Post author:
  • Post last modified:April 15, 2021
  • Post category:General
  • Reading time:6 mins read

In an application development, there may be situations where you need to create a similar table of the table which is already present in the database. In other words, you need to create a duplicate or clone of the existing table. The methods to duplicate or clone SQL table vary from database to database. For example, data warehouse such as Snowflake, Redshift provide methods which are not present in any other databases.

In this article, we will check basic methods that you can use to clone or duplicate SQL tables with or without data.

How to Duplicate or Clone SQL Tables?

Creating duplicate table in SQL is quite easy. You can easily run a couple of commands to best suit your cloning needs. But, commands may slightly vary depending on the database you are working on.

Following are the commonly used methods to duplicate or clone existing table.

CREATE TABLE AS method is one of the widely used and supported in almost all popular databases.

There other methods as per different databases are:

Let us check these methods in brief.

CREATE TABLE AS Method to Duplicate or Clone Table

CREATE TABLE AS (CTAS) is a common method to create a new table based on a query. You can use this method to create tables with data or without data. The query is mandatory to use CTAS.

The following statement uses CTAS to create table with data.

CREATE TABLE anotherTable 
AS 
SELECT * FROM mainTable;

The following statement uses CTAS to create only table structure.

CREATE TABLE anotherTable 
AS 
SELECT * FROM mainTable
LIMIT 0;

We have used LIMIT to restrict data. However, you can use database supported methods to restrict data.

SELECT INTO to Duplicate or Clone a Table

Databases such as SQL Server, Redshift etc. support SELECT INTO methods to crate duplicate or clone a table. SELECT INTO in the database such as Redshift facilitates fast bulk data copy to a new tables from a query.

Following statement uses SELECT INTO to create a new table RedshiftTestTable3 in Amazon Redshift. The statement will create a table with data. However, you can create an empty table with the help of LIMIT option to restrict data.

SELECT 
	ID,
	name,
	age
INTO RedshiftTestTable3
FROM RedshiftTestTable;

CREATE TABLE LIKE Method to Create a Table

CREATE TABLE LIKE method is available in databases such as MySQL and Redshift. The CREATE TABLE LIKE method creates a new table by inheriting settings from the parent table.

The following statement creates a “RedshiftTestTable_bkp” table by copying settings from the exiting “RedshiftTestTable” table.

CREATE TABLE RedshiftTestTable_bkp (LIKE RedshiftTestTable);

CREATE TABLE LIKE method creates the structure by copying settings from the parent table. However, it does not copy the data into a new table.

CREATE TABLE with CLONE Option

The CREATE TABLE with CLONE option creates a copy of an existing object in the Snowflake. This is one of the best and handy features available in Snowflake. This command is primarily used for creating zero-copy clones of databases, schemas, and tables; however, it can also be used to quickly/easily create clones of other schema objects such as external tables.

Following statement clone “orders” table.

create table orders_clone clone orders;

Hope this helps 🙂

Tags: