SQL - CREATE Table

SQL, in relational databases, is used to store the data in the form of some structures. These structures are nothing but simple tables containing data in the form of fields and records. Here, a field is a column defining the type of data to be stored in a table and record is a row containing actual data. SQL provides various queries to interact with the data by creating tables, updating them, deleting them etc.

To create a table in SQL, the CREATE TABLE statement is used.

One can create any number of tables in an SQL Server database. However, a pmit exists on the number of objects that can be present in a database. Including tables, views, indexes etc., a database cannot exceed 2,147,483,647 objects. Therefore, a single user-defined table can define a maximum of 1024 columns.

An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column s data type. Note that each table must be uniquely named in a database.

Syntax

Following is the basic syntax of a CREATE TABLE statement −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE is the keyword telpng the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the pst defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example.

Example

The following code block is an example, which creates a CUSTOMERS table with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Verification

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use the EXEC sp_help command as follows −

EXEC sp_help CUSTOMERS;

The table displayed contains the structure of the table created: column names, their respective data types, constraints (if any) etc.

+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID      | int(11)       | NO   | PRI |         |       |
| NAME    | varchar(20)   | NO   |     |         |       |
| AGE     | int(11)       | NO   |     |         |       |
| ADDRESS | char(25)      | YES  |     | NULL    |       |
| SALARY  | decimal(18,2) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.

Creating a Table from an Existing Table

Instead of creating a new table every time, one can also copy an existing table and its contents including its structure, into a new table. This can be done using a combination of the CREATE TABLE statement and the SELECT statement. Since its structure is copied, the new table will have the same column definitions as the original table. Furthermore, the new table would be populated using the existing values from the old table.

Note − As it is a completely new table, any changes made in it would not be reflected in the original table.

Syntax

The basic syntax for creating a table from another table is as follows −

CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
[WHERE CONDITION];

Here, column1, column2... are the fields of the existing table and the same would be used to create fields of the new table.

Example

Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY −

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM CUSTOMERS;

Output

This would create a new table SALARY which will have the following records −

+----+----------+
| ID | SALARY   |
+----+----------+
|  1 |  2000.00 |
|  2 |  1500.00 |
|  3 |  2000.00 |
|  4 |  6500.00 |
|  5 |  8500.00 |
|  6 |  4500.00 |
|  7 | 10000.00 |
+----+----------+