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.