Sql CREATE TABLE

Sql CREATE TABLE
Nikhil
Published on 2021-08-14 15:00:09

Parameter Details

  • tableName The name of the table
  • columns Contains an 'enumeration' of all the columns that the table have. See Create a New Table for more details.

The CREATE TABLE statement is used create a new table in the database. A table definition consists of a list of columns, their types, and any integrity constraints.

Create Table From Select

You may want to create a duplicate of a table:

CREATE TABLE ClonedEmployees AS SELECT * FROM Employees;

You can use any of the other features of a SELECT statement to modify the data before passing it to the new table. The columns of the new table are automatically created according to the selected rows.

CREATE TABLE ModifiedEmployees AS
SELECT Id, CONCAT(FName," ",LName) AS FullName FROM Employees
WHERE Id > 10;

Create a New Table

A basic Employees table, containing an ID, and the employee's first and last name along with their phone number can be created using

CREATE TABLE Employees(
Id int identity(1,1) primary key not null,
FName varchar(20) not null,
LName varchar(20) not null,
PhoneNumber varchar(10) not null
);

This example is specific to Transact-SQL

CREATE TABLE creates a new table in the database, followed by the table name, Employees

This is then followed by the list of column names and their properties, such as the ID

Id int identity(1,1) not null

Value Meaning

Id the column's name.
int is the data type.

identity(1,1) states that column will have auto-generated values starting at 1 and incrementing by 1 for each new row. primary key states that all values in this column will have unique values not null states that this column cannot have null values

CREATE TABLE With FOREIGN KEY

Below you could find the table Employees with a reference to the table Cities.

CREATE TABLE Cities(
CityID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(20) NOT NULL,
Zip VARCHAR(10) NOT NULL
);

CREATE TABLE Employees(
EmployeeID INT IDENTITY (1,1) NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
PhoneNumber VARCHAR(10) NOT NULL,
CityID INT FOREIGN KEY REFERENCES Cities(CityID)
);

Here could you find a database diagram.

create table in sql

The column CityID of table Employees will reference to the column CityID of table Cities. Below you could find the syntax to make this.

CityID INT FOREIGN KEY REFERENCES Cities(CityID)

Value Meaning

  • CityID Name of the column
  • int type of the column
  • FOREIGN KEY Makes the foreign key (optional)
  • REFERENCES Makes the reference
  • Cities(CityID) to the table Cities column CityID

Important: You couldn't make a reference to a table that not exists in the database. Be source to make first the table Cities and second the table Employees. If you do it vise versa, it will throw an error.

Duplicate a table

To duplicate a table, simply do the following:

CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;

Create a Temporary or In-Memory Table

PostgreSQL and SQLite

To create a temporary table local to the session:

CREATE TEMP TABLE MyTable(...);

SQL Server

To create a temporary table local to the session:

CREATE TABLE #TempPhysical(...);

To create a temporary table visible to everyone:

CREATE TABLE ##TempPhysicalVisibleToEveryone(...);

To create an in-memory table:

DECLARE @TempMemory TABLE(...);

ATutorialHub Related Guide

Comments (8)

Leave a Comment

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

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website