Introduction
Sometimes their is need to update the existing
table in sql server database such as to add, remove column of existing table
or change the size or type of data type of table Column. I have explained Alter
Statement of Sql serevr with syntax and examples to easy understand. let us I
will start it with Basics.
What is Alter Statement in Sql Server ?
The ALTER TABLE statement allows user to modify an existing table such as to
add, modify or drop a column from an existing table.
Syntax
Alter
table
table_name
...(list
of
statements
to
alter)
In the above Syntax Alter is the command or sql statement ,table is the keyword
and table_name is the name of the existing table in sql server database which
you wish to make modification.
Example
Suppose the table_name is emp then query will
be:
Alter
table
emp (list
of
statements
to
alter)
let us see the examples
First create the table in sql server database
named employee as
CREATE
TABLE
employee
(
id
int
not
null,
Name
varchar
(50)
,
city
varchar(50)
,
department
varchar
(10),
CONSTRAINT
employees_pk
PRIMARY
KEY
(id)
);
Then run a query select *from employee the output should be like this.
![Alter-Table-In-SQL-Server.jpg]()
Adding column's to a table
To add a single column to an existing table, the ALTER TABLE syntax is:
ALTER
TABLE
table_name
ADD
column_name
column-definition;
Example
ALTER
TABLE
employee
ADD
salary
varchar(50);
This will add a column salary to the existing employee table. To add multiple
columns to an existing table, the ALTER TABLE syntax is:
alter
table
table_name
add
(column_1
column-defination,
column_2
column-defination ,
column_n
column-defination)
Example
alter
table
employee add
(region
varchar
(50),email
Varchar (50))
This will add the two columns region and email
to the existing employee table.
Modifying Column or Columns of a table
To modify a column in an existing table, the
ALTER TABLE syntax is:
ALTER
TABLE
table_name
MODIFY
column_name
column_type;
Example
ALTER
TABLE
employee
MODIFY
salary
varchar(100)
;
The above query will modify the size of salary column to 100,also you can chnage
the datatype name according to your requirement.
Drop column of a existing table
you can also drop the columns of a existing
table,the syntax is..
ALTER
TABLE
table_name
DROP
COLUMN
column_name;
example
ALTER
TABLE
employee
DROP
COLUMN
salary;
The above query will drop the column salary
from existing employee table
Reference
http://tempuri.org/tempuri.html