Add Column in SQL Table

Overview: You will learn how to add column in SQL table, you can add one or more columns in SQL table

To add column in existing table is one of the most frequent requirement by users and developer. In this tutorial we are going to let you know how you can add column in existing SQL table. Use ALTER TABLE statement to ADD Column.

Note: New column automatically added to the end of the table

Below are the syntax to add one column in SQL table

Syntax:

ALTER TABLE table_name
ADD column_name data_type constraint_name;

Below are the syntax to add more then one columns in SQL table

Syntax:

ALTER TABLE table_name
ADD column_name1 data_type1 constraint_name1;
    column_name2 data_type2 constraint_name2;
    column_name3 data_type3 constraint_name3;
    column_name4 data_type4 constraint_name4;

Lets take few examples to understand more better how you can ADD columns in SQL table

Example #1: Add One column

Let’s create one table using CREATE TABLE statement in SQL, table name is employee.info

CREATE TABLE employee.info (
            Employee_Name VARCHAR ,
            Date_of_join DATE NOT NULL,
            Date_of_leaving DATE NOT NULL
            );

After creating above table lets try to insert new column called Address for employee

ALTER TABLE employee.info
ADD Address VARCHAR (200) NOT NULL;

Example #2: Add two column

Lets add two column in employee.info table, then the syntax will be as below

ALTER TABLE employee.info
ADD Mobile_number INT,
    PIN INT,
    Employee_coutry VARCHAR 

In above example we have added three column  ( Mobile_number, PIN , Employee_coutry ) with different datatype. This command will insert and add these three column at the end of the table 

Note: if you would like to drop a column which you have created but nor in use then you can use Drop command.

Example #2: How to change data type of Added column

Suppose you have added one column with name Date_of_Birth and you have added date as data type as show below code

ALTER TABLE employee.info 
ADD Date_of_Birth Date

But you notice that you want to change date type to year, then you can use below command with Alter column. ALTER Column used to modify any column in table, the definition of column like data type and constraints.

ALTER TABLE employee.info 
ALTER COLUMN Date_of_Birth year;

Conclusion:

You can use Add column during development of procedure where you need to create column for some specific calculation and then you can drop that column using above command, it will helpful to create column at run time and also during query analysis.