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.
Table of contents
Let’s get to know how to create table sand use several options by going over some create table examples.
SQL CREATE TABLE Syntax
The basic syntax to create a table is
CREATE TABLE TableName ( 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.
CREATE TABLE Person ( 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.
CREATE TABLE Product ( ProductID INT NOT NULL PRIMARY KEY, 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.
- Create the Table.
- 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:
CREATE TABLE Person ( 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.
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.
Leave a Reply