SQL CREATE TABLE

The SQL CREATE TABLE Statement

The CREATE TABLE statement establishes a new table within a database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ….
);

The column parameters define the names of the table’s columns.

The datatype parameter determines the type of data each column can store (e.g., varchar, integer, date, etc.).

Tip: For a comprehensive overview of available data types, refer to our complete Data Types Reference.

The provided example establishes a table named “Persons” with five columns: PersonID, LastName, FirstName, Address, and City.

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

The PersonID column is set to type int for storing integers.

The LastName, FirstName, Address, and City columns are designated as varchar to accommodate character data, with a maximum length of 255 characters.

The resulting “Persons” table, currently empty, will be structured as described.

PersonID	LastName	FirstName	Address	City

Tip: You can populate the empty “Persons” table with data using the SQL INSERT INTO statement.

CREATE TABLE can also duplicate an existing table, inheriting its column definitions.

You can choose to replicate all columns or specify particular ones.

When you create a new table from an existing one, it inherits the data from the original table.

CREATE TABLE new_table_name AS
    SELECT column1, column2,…
    FROM existing_table_name
    WHERE ….;

The provided SQL command generates a new table named “TestTables”, which mirrors the structure of the “Customers” table.

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;