Sometimes there 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 server 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
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.
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
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)
);
Adding column's to
a table
to add a single column to an existing table, the ALTER TABLE syntax is:
to add a single column to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
ADD column_name column-definition;
Example
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:
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-definition,
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 change the data type name according to your requirement.
Drop column of a existing table
The above query will modify the size of salary column to 100,also you can change the data type 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
Post a Comment