SQLite: CREATE TABLE AS Statement
This SQLite tutorial 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.
Advertisements