SQL CREATE TABLE

If you want to store, manipulate, or retrieve data in a SQL database, the most fundamental element you will be interacting with is the table. Hence, knowing how to create a table in SQL is a required skill for any database developer, data analyst, or database scientist.

SQL CREATE TABLE Syntax

The Create Table SQL command is used to create a new table in the database. The basic syntax is as follows:

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

In the create table syntax above, the table_name is the name of the table you want to create, while column1, column2, etc., are the columns you want to include in the table. The datatype indicates the type of data that the column can hold (e.g., varchar, integer, date, etc.). Now let’s learn how to create a table in SQL.

SQL Create Table Example

Let's take a look at a practical SQL CREATE TABLE example. Suppose we want to create a table called "Employees" that will store the ID, name, age, and salary of each employee.

Here is the SQL command to create table:

CREATE TABLE Employees (
    ID int,
    Name varchar(255),
    Age int,
    Salary decimal(10, 2)
);

In this create table command, we create a table called "Employees" with four columns: ID, Name, Age, and Salary. The ID and Age columns will store integer values. The Name column will store strings of up to 255 characters. And the Salary column will store decimal numbers with two digits after the decimal point.

After this SQL create table command is executed, a new table named "Employees" will be created in the database with the defined structure but no data. SQL Server will show the following message:

Let’s add some data into our new table using SQL INSERT command:

INSERT INTO Employees (ID, Name, Age, Salary)
VALUES
    (1, 'NAME 1', 30, 50000.00),
    (2, 'NAME 2', 35, 60000.00),
    (3, 'NAME 3', 40, 70000.00);

Now run this SELECT statement to see the inserted data!

SELECT * FROM Employees

The output of our SQL Create Table Example will be:

Sometimes, you might need to create a temporary table in SQL for various reasons, such as storing intermediate results, or when you need a table for just a specific session. For such cases, we can use Create Temp Table SQL Server command (it doesn’t include “Temp” inside the command, you will see it later).

They're similar to regular tables but have a few key differences. One significant difference is that they're stored in tempdb and are deleted automatically when they're no longer used. SQL Server has two types of temp tables, Local Temp Table and Global Temp Table.

A SQL Local Temp Table is only visible to the current session or the procedure that created it. It is deleted once the session that created it ends.

A SQL Global Temp Table is visible to all SQL Server connections, and it is deleted when the last connection which is referencing the table is closed.

The local temp tables are defined with a single hashtag prefix ("#") while global temp tables are created with a double hashtag prefix ("##").

The syntax is the same as the SQL CREATE TABLE command, you just add a '#' before the name of the table ( double hashtag “##” in case of Global Temp table ).

For instance, suppose we want to create a temporary table called "TempEmployees" that will store the ID and Name of each employee.

SQL Temp Table Example:

CREATE TABLE #TempEmployees (
    ID int,
    Name varchar(255)
);

After the session or procedure ends, the "TempEmployees" table will be automatically deleted.

There are situations when you need to create a table based on the structure and data of another table. You can create a new table in SQL with the same column definitions as an existing table by using the SQL Server SELECT INTO command.

Let's say we want to create a table in SQL called 'BackupEmployees' that has the same structure and data as 'Employees'. Here is the command:

SELECT *
INTO BackupEmployees
FROM Employees;

The resulting 'BackupEmployees' table would look identical to our Employees' table:

While working with SQL, it's common to want to ensure that a table doesn't already exist before attempting to create it. Attempting to create table in SQL with a name that's already used in the database will result in an error. To prevent this, you can use the SQL create table IF NOT EXISTS command.

For instance, if we want to create table SQL "Employees" but only if it doesn't already exist.

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
CREATE TABLE Employees
(
    ID int,
    Name varchar(255),
    Age int,
    Salary decimal(10, 2)
);

In this case, SQL Server will first check if there's a table named 'Employees'. If there isn't, it will create table in SQL.

A PRIMARY KEY is a field or can be a combination of fields in a table that uniquely identifies each record in the table. When you create table in SQL, you can define a primary key using the PRIMARY KEY constraint.

For instance, if we want to create the "Employees" table and set the ID as the primary key, we would use the following SQL CREATE TABLE statement:

CREATE TABLE Employees (
    ID int PRIMARY KEY,
    Name varchar(255),
    Age int,
    Salary decimal(10, 2)
);

In this case, the ID field will be the primary key for the "Employees" table, which means it will uniquely identify each record in the table, and it cannot have NULL values.

Constraints in SQL are used to specify rules for the data in a table. When creating a table in SQL, you can set constraints on the columns to ensure data integrity. The PRIMARY KEY we just discussed is an example of a constraint. But there are several others including UNIQUE, NOT NULL, CHECK, and FOREIGN KEY.

When you create table in SQL, you can specify these constraints. For example, let's create a 'Projects' table with some constraints:

CREATE TABLE Projects
(
    ProjectID INT PRIMARY KEY,
    EmployeeID INT NOT NULL,
    ProjectName VARCHAR(50) UNIQUE,
    StartDate DATE CHECK (StartDate >= '01/01/2000'),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

In this table, 'ProjectID' is the primary key. 'EmployeeID' is a foreign key that links to the 'ID' in the 'Employees' table. 'ProjectName' must contain unique values, and 'StartDate' must be a date in or after the year 2000.