Use SQL CREATE TABLE AS statement to create a table from an existing table by copying the existing table’s columns.
What happens to the METADATA and DATA when creating a table by copying from existing table?
Metadata like all/selected columns and its datatype (including size) gets copied except constraints excluding NOT NULL constraint.
Means out of all constraints (primary key,foreign key,unique,check and not null) only not null constraint get copied for the column in new table as like the column in existing table.
Data (full/partial/no data) gets copied as mentioned in the where clause of the select statement.
All columns & data in the existing table gets copied to the new table for the below statement.
CREATE TABLE new_table
AS (SELECT * FROM existing_table);
Selected columns & data in the existing table gets copied to the new table for the below statement.
CREATE TABLE new_table
AS (SELECT column_1, column_2, … column_n
FROM existing_table WHERE condition );
Selected columns & data from multiple existing tables gets copied to the new table for the below statement.
CREATE TABLE new_table
AS (SELECT a.column_1, b.column_2, … c.column_n
FROM existing_table_1 a, existing_table_2 b, … existing_table_n c WHERE condition );
And Below statement is used to create table without DATA.
CREATE TABLE new_table
AS (SELECT *
FROM existing_table WHERE 1=2);
The above statement creates a new table by copying all columns and not null constraint from existing table.
SELECT fetches all the data and for each record it applies & checks the condition. Since the condition fails it does not return the data in output.
