SQL ALTER TABLE Statement Add Column Syntax

To add a column to a table using SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD command to tell the RDBMS that we want to add a column.

Syntax

The SQL syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"
ADD "column_name" "Data Type";

Examples

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	datetime

Example 1: Add one column to a table

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);

The resulting table structure is:

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	datetime
Gender	char(1)

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

Example 2: Add multiple columns to a 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	datetime
Gender	char(1)
Email	char(30)
Telephone	char(20)

SHARES

Share

Pin it

Tweet

Share

Share

Share

Buffer

Print

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.