Fom example,
CREATE TABLE employee_info As
SELECT emp.id, emp.name, dp.name FROM employee emp, department dp WHERE emp.dp_id =dp.id;
The above CTAS statment create a new table named 'employee_info' and store the result of SELECT statement into that table.
Here are some common use of CTAS statement:
- Store the result of select statement into a table.
- create a duplicate table for backup purpose.
- Reorganize existing table for better performance.
- Can't create indexes ( in case of table duplication or Reorganization).
- Can't create primary, unique or foreign key constraints ( in case of table duplication or Reorganization). But it can create 'NOT NULL' constraint.
- Though CTAS mirrors metadata structure, it can't mirror 'DEFAULT' value checker for columns.