Hello Friends, today I am going to share SQL syntax to create Table by copying data from existing table. Some times it require to create new table for data according to data nature.
Say, you have CUSTOMER table which contains data for MALE, FEMALE and CHILD category. Now you require to create three (3) new table as named CUSTOMER_MALE, CUSTOMER_FEMALE and CUSTOMER_CHILD
My existing table name is DEMO_CUTOMERS and I will create new Customer table
TASK 1
CREATE NEW TABLE WITH ALL DATA FROM EXISTING TABLE
Connect to Oracle Express Edition 11g Database and using my schema name PIJUSH
See my existing Table by using SQL comment SELECT * FROM DEMO_CUSTOMERS
Query which I used for copying data from one table to new table
CREATE TABLE NEWCUSTOMERTABLE AS
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME
FROM DEMO_CUSTOMERS
Run the query
Query create new table names as NEWCUSTOMERTABLE
Now view the data in new table, run query SELECT * FROM NEWCUSTOMERTABLE
Result is
TASK 2
CREATE NEW TABLE WITH SPECIFIC DATA FROM EXISTING TABLE
You will notice that, in case of first query, all customer data copy from old table and paste in new table. But for second query, only specific data as mentioned in condition "WHERE CUST_FIRST_NAME = 'William' inserted into new table.
Create second table where customer name is WILLIAM
Query which I used
CREATE TABLE NEWCUSTOMERTABLE_2 AS
SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME
FROM DEMO_CUSTOMERS WHERE CUST_FIRST_NAME = 'William'
Run query
See the result
You will notice that, in case of first query, all customer data copy from old table and paste in new table. But for second query, only specific data as mentioned in condition "WHERE CUST_FIRST_NAME = 'William' inserted into new table.
