# How to create a new table with SQL CREATE TABLE

SQL `CREATE TABLE` is used to create a new table, which must be unique within the database. Important parameters are defined when the table is created, and it’s possible to use an existing table as a basis.

## What is SQL CREATE TABLE?

Once you’ve created an [SQL database](https://www.ionos.com/digitalguide/server/configuration/sql-drop-database/) using the [Structured Query Language](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-with-examples/), you can create a new table. This table serves as the repository for your data records and defines the structure in which the data is organized. Tables are composed of **fields or columns that hold specific data types** and **rows where this data is recorded**. To create a new table, you use the SQL `CREATE TABLE` statement. This command not only creates the table but also sets its structure. Afterward, you can fill the table with the relevant data.

## Syntax and function

Just by looking at the syntax of SQL `CREATE TABLE`, you can see that data types and names are mandatory for correctly creating a new table from the start. The syntax is basically as follows:

```sql
CREATE TABLE name_of_table ( 
column1 data type, 
column2 data_type, 
column3 data_type,
…
);
```

Use the [SQL command](https://www.ionos.com/digitalguide/server/configuration/sql-commands/) `CREATE TABLE` to create a new table, specifying its name in the first line. Define the table’s structure within the parentheses. For example, choose names for `column1`, `column2`, etc., and **specify the data types that can be stored in each column**. Common data types include `VARCHAR` (a string that can contain letters, numbers, or special characters), `BOOLEAN` (for the Boolean values true or false), `INT` (for numerical values) or `DATE` (for dates).

## Example of a new table

To illustrate how SQL `CREATE TABLE` works, we’ll use a simple example to show you what such a table might look like. We’ll create an employee directory that contains an employee ID, surname, first name, address, city, and age. We need a total of **six columns** to store different data types. The appropriate code looks like this:

```sql
CREATE TABLE Employees ( 
ID INT NOT NULL, 
Last name VARCHAR(50), 
First name VARCHAR(50), 
Address VARCHAR(50), 
City VARCHAR(50), 
Age INT NOT NULL
);
```

For ID and age, we select the data type `INT`, which contains numbers. The addition `NOT NULL` specifies that these fields must not be left blank. The remaining columns `Last name`, `First name`, `Address` and `City` may contain letters, numbers and special characters.

## Combination with the IF NOT EXISTS command

Each table must be **unique** within a database so that the system can access it. If you try to create a new table with SQL `CREATE TABLE` and choose a name that already exists, you will get an error message like `Error: table already exists`. To avoid this, you can add the optional command `IF NOT EXISTS`. This command first checks whether a table with the same name already exists. If it does not, it creates a new table with the specified parameters. For our example above, it looks like this:

```sql
CREATE TABLE IF NOT EXISTS employees ( 
ID INT NOT NULL, 
Last name VARCHAR(50), 
First name VARCHAR(50), 
Address VARCHAR(50), 
City VARCHAR(50), 
Age INT NOT NULL
);
```

## Create a new table based on an existing table

Even though the statement is quite simple, it can be tedious to create several similar tables. SQL `CREATE TABLE` also gives you the option of **using an existing table** to create a new one. You can choose whether to create an exact copy or only take certain parameters into account. We’ll show you both methods. In the first case, we create an employee list for another branch. This table will be set up identically but requires a new name. Here is the code:

```sql
CREATE TABLE New_employee_list
AS
SELECT *
FROM Employees;
```

The asterisk indicates to the system that it should accept all columns.

In the following example, we create a customer list. This should also contain the columns for ID, surname, first name, address and city, but the date of birth should not be created. We therefore specify which columns are to be copied:

```sql
CREATE TABLE Customer_list
AS
SELECT ID, last name, first name, address, city 
FROM Employees;
```

## Similar commands to SQL CREATE TABLE

In addition to the `CREATE DATABASE` statement already mentioned, there are other important statements for working with SQL `CREATE TABLE`. These include [SQL DELETE](https://www.ionos.com/digitalguide/server/configuration/sql-delete/) to delete entries from a table, and [SQL Aliases](https://www.ionos.com/digitalguide/server/configuration/sql-aliases/) to temporarily rename tables or columns.

Tip Your personal server: With [SQL Server Hosting](https://www.ionos.com/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS, you can choose between MSSQL, MySQL and MariaDB and benefit from numerous useful features. Find the right solution for your needs!


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/sql-create-table/](https://www.ionos.com/digitalguide/server/configuration/sql-create-table/) for AI/LLM consumption.