copy table in redshift

How To Copy Table in Redshift

Sometimes you may need to duplicate table in Redshift or create backup table in Redshift. In this article, we will look at how to copy table in Redshift.


How To Copy Table in Redshift

There are three ways to copy table in redshift. We will look at each of these methods.


Copy Table Structure & Data

You can use CREATE TABLE .. AS SELECT … statement to copy table structure and data in Redshift.

Here is the syntax to copy data in Redshift this way.

CREATE TABLE new_table AS SELECT * FROM original_table;

In the above query, you need to mention the names of original table as well as new table that you want to copy data to.

Please note, the above statement will only copy column definitions, null constraints and default constraints. It will not copy indexes and other attributes.

Also, the above statement can take a lot of time in case of big tables.

Let us say you have the following table.

# create table sales(id int, order_date date, amount int);

# insert into sales(id,order_date, amount) 
    values(1,'2020-12-10',150),
          (2,'2020-12-11',250),
          (3,'2020-12-12',350);

# select * from sales;
 id | order_date | amount
----+------------+--------
  1 | 2020-12-10 |    150
  2 | 2020-12-11 |    250
  3 | 2020-12-12 |    350

Here is the SQL to copy this table in Redshift.

# create table new_sales as select * from sales;
# select * from new_sales;
 id | order_date | amount
----+------------+--------
  1 | 2020-12-10 |    150
  2 | 2020-12-11 |    250
  3 | 2020-12-12 |    350

You can also choose to copy only specific rows from original table by adding a WHERE clause to your select statement. Here is an example to copy only those rows where product=’ABC’.

CREATE TABLE new_sales AS 
SELECT * FROM sales 
WHERE product='ABC';

Also read : How to Remove NOT NULL Constraint in Redshift


Copy Table Structure Only

If you want to copy only the structure, use CREATE TABLE … LIKE …

Here is the syntax

CREATE TABLE new_table (LIKE original_table);

In the above statement you need to mention original and new tables’ names.

Please note, it will create an empty table that has the same structure as your original table.

Also Read : How to Create Table in Redshift


Copy Data to Another Table

Let us say you already have a table in place and want to copy data from another table. In such cases, you can use INSERT INTO … SELECT … statement.

Here is the syntax for it.

INSERT INTO new_table SELECT * FROM original_table;

You can choose to copy only specific rows from original table by adding a WHERE clause in the select statement.

INSERT INTO new_sales 
SELECT * FROM sales
WHERE product='ABC';

The above article should help you create backup table in Redshift.

Leave a Reply

Your email address will not be published. Required fields are marked *