SQL Create table from another table

SQL Create table from another table 

Create a table from another table (CTAS):- We can create a table based on query

1) By false Condition:- Create structure only from a table:- For creating structure only, need to pass false condition so that data will not be selected by query

Examples:-
create table emp1 as select * from emp where 1=2;

2) By rownum :- We can create structure only by rownum condition as well

Example:-
 create table emp7 as select * from emp where rownum<1 span="">

Create a copy of table (with data) from another table:- For creating a table with data from another table, we need not to mention any condition or we can give any true condition so that data will also be selected by query. We create copy of table for backup purpose as well.
  
Examples:-
1) Without any condition :-
create table emp4 as select * from emp;

 2) With true condition :-
create table emp4 as select * from emp where 1=1;

Create a table from any query :-

Examples:-
create table emp6 as select empno,ename,job,sal from emp where sal>1000 and deptno in (10,20);

create table emp_dept as
select a.empno,a.ename,a.job,a.sal,b.deptno,b.dname,b.loc
from emp a ,dept b where a.deptno=b.deptno;

No comments:

Post a Comment