Create Table as Select - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL Usage
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

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Five star feature compatibility

Five star automation level

N/A

N/A

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

The following example creates a table based on an existing table and include data from all columns.

CREATE TABLE EMPS
AS
SELECT * FROM EMPLOYEES;

The following example creates 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.

MySQL Usage

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

  • The standard requires parentheses around the SELECT statement; MySQL doesn’t.

  • The standard requires the WITH [ NO ] DATA clause; MySQL doesn’t.

Examples

The following example creates a table based on an existing table and include data from all columns.

CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES;

The following example creates 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;