Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

CREATE TABLE AS SELECT statement

With Amazon DMS, you can create a new table in a target database by selecting data from one or more tables in a source database using the Oracle and PostgreSQL CREATE TABLE AS SELECT statement. This statement defines a new table by querying data from existing tables, providing a way to replicate table structures and data from a source to a target database.

Oracle usage

The Create Table As Select (CTAS) statement creates a new table based on an existing table. It copies the table DDL definitions (column names and column datatypes) and data to a new table. The new table is populated from the columns specified in the SELECT statement, or all columns if you use SELECT * FROM. You can filter specific data using the WHERE and AND statements. Additionally, you can create a new table having a different structure using joins, GROUP BY, and ORDER BY.

Examples

Create a table based on an existing table and include data from all columns.

CREATE TABLE EMPS
AS
SELECT * FROM EMPLOYEES;

Create a table based on an existing table with select columns.

CREATE TABLE EMPS
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES
ORDER BY 3 DESC

For more information, see CREATE TABLE in the Oracle documentation.

PostgreSQL usage

PostgreSQL conforms to the ANSI/SQL standard for CTAS functionality and is compatible with an Oracle CTAS statement. For PostgreSQL, the following CTAS standard elements are optional:

PostgreSQL CTAS synopsis

CREATE
[ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) |
WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]

Examples

PostgreSQL CTAS.

pg_CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES;
pg_CREATE TABLE EMPS AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY 3 DESC;

PostgreSQL CTAS with no data.

pg_CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES WITH NO DATA;

For more information, see CREATE TABLES in the PostgreSQL documentation.