SQLite CREATE TABLE AS Keyword

The SQLite CREATE TABLE AS keyword is used to create a table from an existing table by copying the existing table's columns. When creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT statement).

Syntax

The syntax for using CREATE TABLE AS keyword in SQLite given below:

/* copy all columns from a table */
CREATE TABLE new_table
  AS SELECT * FROM old_table;

/* copy selected columns from a table */
CREATE TABLE new_table
  AS SELECT column_1, column2, ...
      FROM old_table;

/* copy columns from multiple table */
CREATE TABLE new_table
  AS SELECT column_1, column2, ...
      FROM old_table_1, old_table_2, ...;

Example:

Consider a database table called Employee with the following records:

• Copy all columns of a table: The below statement is used to create a new table called Employee_PT by copying all columns of Employee table. The new table will contain those records of Employee table where the age of the employee is less than 28.

  CREATE TABLE Employee_PT
    AS SELECT * FROM Employee
      WHERE Age < 28;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  3	Jo	Paris	27	2800

• Copy selected columns of a table: It is also possible to copy selected columns when using CREATE TABLE AS statement. In the example below, the columns which are copied are: EmpID, Name and City.

  CREATE TABLE Employee_PT
    AS SELECT EmpID, Name, City FROM Employee
      WHERE Age < 28;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City
  1	John	London
  2	Marry	New York
  3	Jo	Paris

• Using AS clause: The AS clause can be used to rename the column name. See the example below:

  CREATE TABLE Employee_PT
    AS SELECT EmpID, Name AS EmployeeName, City FROM Employee
      WHERE Age < 28;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	EmployeeName	City
  1	John	London
  2	Marry	New York
  3	Jo	Paris

• Copy columns from multiple tables: Consider one more table called Contact_Info with the following records:

  Phone_Number	EmpID	Address	Gender
  +1-8054098000	2	Brooklyn, New York, USA	F
  +33-147996101	3	Grenelle, Paris, France	M
  +31-201150319	4	Geuzenveld, Amsterdam, Netherlands	F
  +86-1099732458	6	Yizhuangzhen, Beijing, China	M
  +65-67234824	7	Yishun, Singapore	M
  +81-357799072	8	Koto City, Tokyo, Japan	M

  
  

  The below statement is used to create a new table called Employee_PT which will contain selected columns of Employee and Contact_Info tables. The new table will contain records based on INNER JOIN.

  CREATE TABLE Employee_PT
    AS SELECT A.EmpID, A.Name, A.City, B.Address 
      FROM Employee A
      INNER JOIN Contact_Info B
      ON A.EmpID = B.EmpID;
  
  -- see the result
  SELECT * FROM Employee_PT;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Address
  2	Marry	New York	Brooklyn, New York, USA
  3	Jo	Paris	Grenelle, Paris, France
  4	Kim	Amsterdam	Geuzenveld, Amsterdam, Netherlands
  6	Huang	Beijing	Yizhuangzhen, Beijing, China