You can use the SQL CREATE TABLE command to create a new table in the database.  When you create a table you define the table name, the columns, data types, and any constraints against them.

You initially create tables empty, so you’ll need to use the insert statement to add data into it.  If you’re using Azure Synapse Analytics (SQL Datawarehouse) you can also use the CREATE TABLE AS SELECT (CTAS) statement to create and insert data at the same time.  This is very handy.

Let’s get to know how to create table sand use several options by going over some create table examples.


The basic syntax to create a table is

   Column1 dataType constraints,
   Column2 dataType constraints,

Create a Simple Table

In this example we’ll create a “Person” table containing the columns FirstName, LastName, and Age.  In this example we didn’t use any constraints.

   FirstName Varchar(30),
   LastName  Varchar(40),
   Age       Int

When creating a SQL relational table, the first thing you do is specify the table name.  You see this right after the CREATE TABLE keywords.

Then we create columns.   You enclose the columns in parentheses and separate them by a comma.

Columns are identified by their name but characterized by their datatype.  There a many data types used to define columns.

The most popular data types used are:

  • INT – For integers such as age
  • VARCHAR – For character values such as a person’s name.
  • DATETIME – To store a calendar date and time.
  • DECIMAL, FLOAT – Used to store fractional numbers such as 3.5.

Tip!  To learn more about data types, check out my article SQL Data Types

Create a Table with Constraints

In this example we’ll create a table using constraints.  You see them at the end of the data type specification.  The constraints we’re using are:

  • Nullability Constraints – we can specify whether the column allows NULL.
  • Primary Key – define which column within the table is the primary key. 
  • Unique Key – define which column within the table is the unique key.

Tip!  Read Primary Key vs Unique Key – Data Modeling Tips.

   ProductNumber VARCHAR(10)  NOT NULL UNIQUE,
   Name          VARCHAR(30) NOT NULL,
   Description   VARCHAR(140) NULL,
   Weight        Float NULL

In this example we’re creating a product table.  The ProductID is the table’s Primary Key.  I defined this with two constraints.  First it does not allow NULL.  Second, it is the primary key.  It is the column used to identity rows with the table.

The second column Product Number is also not null, and each value is unique.  These constraints ensure we don’t allow two rows with the same ProductNumber.

The remaining columns, Name, Description, and Weight, all allow NULL.  Meaning, when you go to insert a new row, you don’t need to specify values for these columns.

Adding Data to Table

As I mentioned earlier, when you create a table, it is empty.  If you’re using Azure Synapse Analytics, then checkout CTAS; otherwise follow these steps.

  1. Create the Table.
  2. Insert Data Into the Table.

Suppose we want to create a Person table and populate it from data we just imported from TempPerson.  How can we do that?  Here are the commands:

   FirstName Varchar(30),
   LastName  Varchar(40),
   Age       Int

INSERT INTO Person (FirstName, LastName, Age)
SELECT First, Last, PersonAge
FROM TempPerson

In this example we first create the Person table, then create an insert statement which target the table. 

SQL CREATE TABLE statement with example insert

Notice that columns name match.  We select columns from TempPerson.  Notice here, the columns names don’t match, but there are in the correct order.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SqlServer