Open In App

SQL CREATE TABLE

Improve
Improve
Like Article
Like
Save
Share
Report

In the SQL database for creating a table, we use a command called CREATE TABLE.

SQL CREATE TABLE Statement

A Table is a combination of rows and columns. For creating a table we have to define the structure of a table by adding names to columns and providing data type and size of data to be stored in columns.

Syntax:

CREATE table table_name

(

Column1 datatype (size),

column2 datatype (size),

.

.

columnN datatype(size)

);

Here table_name is name of the table, column is the name of column

SQL CREATE TABLE Example

Let us create a table to store data of Customers, so the table name is Customer, Columns are Name, Country, age, phone, and so on. 

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);

Output:

 

Insert Data  into Table

To add data to the table, we use INSERT INTO, the syntax is as shown below:

Syntax:

//Below query adds data in specific column, (like Column1=Value1)//

Insert into Table_name(Column1, Column2, Column3)

Values (Value1, value2, value3);

//Below query adds data in table in sequence of column name(Value1 will be 

added in Column1 and so on)//

Insert into Table_name

Values (Value1, value2, value3);

//Adding multiple data in the table in one go//

Insert into Table_name

Values (Value01, value02, value03),

(Value11, value12, value13),

(Value21, value22, value23),

(ValueN1, valueN2, valueN3)

Example Query

This query will add data in the table named Subject 

-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

 

Create a Table Using Another Table

We can also use CREATE TABLE to create a copy of an existing table. In the new table, it gets the exact column definition all columns or specific columns can be selected.

If an existing table was used to create a new table, by default the new table would be populated with the existing values ??from the old table.

Syntax:

CREATE TABLE new_table_name AS

    SELECT column1, column2,…

    FROM existing_table_name

    WHERE ….;

Query:

CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;

Output:

  • We can use * instead of column name to copy whole table to another table.

Note : In this query another table will be created with all the columns instead of just two columns.

Syntax :

CREATE TABLE new_table_name AS

    SELECT *

    FROM existing_table_name

    WHERE ….;

Query :

CREATE TABLE customer_copy AS SELECT * FROM customer;

Output :

customer_copy_sql

  • You can also use LIMIT to insert specific number of records from old table. In below example it will populate new table with first three records.

Syntax :

CREATE TABLE <new_table_name> AS SELECT * FROM <old_table_name> LIMIT <numeric_value>;

Query :

CREATE TABLE customer_copy AS SELECT * FROM customer LIMIT 3;

Output :

sql_limit


Last Updated : 30 Aug, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads