SQLite: CREATE TABLE AS Statement

This SQLite post explains how to use the SQLite CREATE TABLE AS statement with syntax and examples.

Description

The SQLite CREATE TABLE AS statement is used to create a table from an existing table by copying the existing table's columns.

It is important to note that 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 the CREATE TABLE AS statement in SQLite is:

CREATE TABLE new_table AS
  SELECT expressions
  FROM existing_tables
  [WHERE conditions];

Parameters or Arguments

new_table

The name of the table that you wish to create.

expressions

The columns from the existing_tables that you would like created in the new_table. The column definitions from those columns listed will be transferred to the new_table that you create.

existing_tables

The existing tables from which to copy the column definitions and the associated records (as per the WHERE clause).

WHERE conditions

Optional. The conditions that must be met for the records to be copied to the new_table.

Note

• The column definitions from the existing_tables will be copied to the new_table.
• The new_table will be populated with records based on the conditions in the WHERE clause.

Example

Let's look at a SQLite CREATE TABLE AS example that shows how to create a table by copying all columns from another table.

CREATE TABLE active_employees AS 
  SELECT *
  FROM employees
  WHERE hire_date IS NOT NULL;

This example would create a new table called active_employees that included all columns from the employees table.

If there were records in the employees table, then the new active_employees table would be populated with the records returned by the SELECT statement.

Next, let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from multiple tables.

For example:

CREATE TABLE active_employees AS
  SELECT employees.employee_id AS "active_employee_id", 
         employees.last_name, employees.first_name, departments.department_name
  FROM employees, departments
  WHERE employees.department_id = departments.department_id
  AND employees.hire_date IS NOT NULL;

This example would create a new table called active_employees based on column definitions from both the employees and departments tables. Notice in this example that we have aliased the employee_id field as active_employee_id since we want the field in the new active_employees table to be called active_employee_id and not employee_id.