SQL ALTER TABLE Add Column Syntax

The SQL syntax for ALTER TABLE Add Column is

ALTER TABLE "table_name"
ADD "column 1" "Data Type"

Let's look at the example. Assuming our starting point is the "customer" table created in the CREATE TABLE section:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	date

Our goal is to add a column called "Gender". To do this, we key in:

MySQL:
ALTER TABLE customer ADD Gender char(1);

Oracle:
ALTER TABLE customer ADD Gender char(1);

SQL Server:
ALTER TABLE customer ADD Gender char(1);

Resulting table structure:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	date
Gender	char(1)

Note that the new column Gender becomes the last column in the customer table.

It is also possible to add multiple columns. For example, if we want to add a column called "Email" and another column called "Telephone", we will type the following:

MySQL:
ALTER TABLE customer ADD (Email char(30), Telephone char(20));

Oracle:
ALTER TABLE customer ADD (Email char(30), Telephone char(20));

SQL Server:
ALTER TABLE customer ADD (Email char(30), Telephone char(20));

The table now becomes:

Table customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	date
Gender	char(1)
Email	char(30)
Telephone	char(20)

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.

<a href="http://www.1keydata.com/sql/alter-table-add-column.html">SQL ALTER TABLE ADD COLUMN Syntax</a>