CTAS - With few additional columns apart from the query

May be once a while requirement but simple trick to remember though...

Wanted to create a table with CTAS (Create Table As Select) to avoid the performance issues using insert statement. But, wanted to create table with few additional columns also instead of just the columns from select statement.

For Ex,.:

Existing Table (EMP) Structure:

Emp_ID number(6)

Emp_Name varchar2(50)

Dept_No number(4)

CTAS Table (NEW_EMP) Needs to Have columns like this:

Emp_ID number(6)

Emp_Name varchar2(50)

Dept_No number(4)

Salary number(8,2)

SQL:

create table new_emp as select emp_id, emp_name, dept_no from emp; -- will only gets those three columns select in the sql.

Following SQL will create the table with four columns with the data type that is needed also.

create table new_emp as

(select emp_id, emp_name, dept_no, cast(1 as number(8,2)) salary

from emp);

cast(1 as number(8,2)) salary --> this trick can be used not only to create a new column but also can be used to modify the existing column data type.

For ex., if the new_emp table emp_id should be of number(9) instead of the source table type number(6), then use the query as showed below:

create table new_emp as

(select cast(emp_id as number(9)) emp_id, emp_name, dept_no, cast(1 as number(8,2)) salary

from emp);

Hope this helps me in future when i get old... :)