Create table with existed table

In earlier post, we have checked about Aliases in SQL and now lets see creating table with existed table.

CREATE TABLE USING EXISTED TABLE WITH SELECT STATEMENT:
We can create a table using existing table [along with data].

Syntax:   Create table <new_table_name> [col1, col2, col3 ... coln] as select * from 
                                                               <old_table_name>;

Ex:    SQL> create table student1 as select * from student;
    
    Creating table with your own column names.
    SQL> create table student2 (sno, sname, smarks) as select * from student;
    
    Creating table with specified columns.
    SQL> create table student3 as select no,name from student;

    Creating table with out table data.
    SQL> create table student2(sno, sname, smarks) as select * from student where 1 = 2;

    In the above where clause give any condition which does not satisfy.Instead of where 1=2 if you specify where 1=1 then condition got satisfied so it ill create table with data.


INSERT INTO TABLE USING EXISTED TABLE WITH SELECT STATEMENT:

Using this we can insert existing table data to another table in a single trip. But the table structure should be same.

Syntax:     Insert into <table1> select * from <table2>;    

Ex:     SQL> insert into student1 select * from student;

     Inserting data into specified columns

     SQL> insert into student1(no, name) select no, name from student;