Alter Table Statement in SQL Server

Sometimes there is a need to update the existing table in a SQL database such as to add, remove column of the existing table or change the size or type of data type of the table Column. I have explained the Alter Statement of SQL with examples to make it easy to understand. Let us start it with Basics.

What is Alter Statement in SQL?

The ALTER TABLE statement allows user to modify an existing table such as to add, modify or drop a column from an existing table.

To demonstrate the Alter statement, 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)
)

Adding column's to a table

To add a single column to an existing table use the following SQL query.

ALTER TABLE employee
ADD salary varchar(50);


You can also add multiple columns to an existing table, The following is the query

Alter Table employee add 
(
Region varchar (50),
Email Varchar (50)
)
 

Modifying Column of an Existing Table

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE Employee
MODIFY salary varchar(100)


The above query will modify the size of salary column to 100.

Drop Column of an Existing Table

You can also drop the columns of a existing table, use the following query

ALTER TABLE employee
DROP COLUMN salary
 
The above query will drop the column salary from existing employee table.

Post a Comment

www.CodeNirvana.in

Protected by Copyscape Online Copyright Protection
Copyright © Compilemode