SQL > SQL ALTER TABLE > Add Column Syntax

Sometimes we wish to add a column to a table. This can be achieved in SQL. To do this, 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.

The SQL syntax for ALTER TABLE Add Column is,

ALTER TABLE "table_name"
ADD "column_name" "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 NameData Type
First_Namechar(50)
Last_Namechar(50)
Addresschar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime

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 NameData Type
First_Namechar(50)
Last_Namechar(50)
Addresschar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime
Genderchar(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 NameData Type
First_Namechar(50)
Last_Namechar(50)
Addresschar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime
Genderchar(1)
Emailchar(30)
Telephonechar(20)

Next: SQL MODIFY COLUMN




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact