Sunday, 28 May 2017

Create a table from another table or from a query in Oracle SQL

Create a table from another table or from a query in Oracle SQL


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;

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;

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...