Copying the Structure with Records

If the structure of one table has to be copied on to another table along with the records in that case the Create table statement is used in combination with the Select statement. The structure beside with the records is copied on to the second table.

Syntax:

CREATE TABLE <tablename2> AS SELECT <columnlist> FROM <tablename1> [WHERE <conditions>]

Example:

Let consider creating a table known as employee1 whose structure is the similar as employee table. To create this use,

CREATE TABLE employee1 AS SELECT * FROM EMPLOYEE;
Displays the feedback, Table created.

The structure of the employee1 would be the similar as the structure of employee table. The records will also be the similar. To view the records of employer table, use
SELECT * FROM employee1; this would display

If only specific columns required to be copied use,

CREATE TABLE employee2 AS SELECT empno,empname FROM EMPLOYEE; # The statements provide above create new tables named by employee1 and employee2 respectively#

In case of employee1 table, the structure that exists in the employee table is copied and the records are inserted. In case of employee2 table, two columns are copied from the employee table with the records and the structure. The above statements can be written in other words.

CREATE TABLE <tablename>
and
INSERT INTO <tablename> SELECT <columnlist> FROM <tablename> AS #explained in the section on Inserting records using SELECT Statement#.