SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Adding , Deleting and Updating a Column in a table

Many times we need to alter the table definition by adding , deleting or updating a column in the table. In this article, I am trying to explain the following :-

1. How to add a column
2. How to update a column
3. How to drop a column

Suppose we have a table say Employee whose structure is given below:-

CREATE TABLE Employee (Emplyeeid int identity(1,1),
Empnumber nvarchar(10),
Firstname nvarchar(50),
Lastname nvarchar(150),
Age int,
Phoneno nvarchar(15),
Address nvarchar(200),
Empdate datetime)

Insert query for Inserting data in the table is given below:-

Insert Into employee Values
('mani123','Manish', 'Arora',27,'245121','Delhi',getdate())

We can see the data in table employee through the following SQL Statement:-

Select * from Employee

Now suppose we want to update the column Firstname of the table Employee by increasing its character limits from 50 to 100.  The SQL statement used for this column updation will be:-

Alter table Employee Alter column Firstname nvarchar(100)

Again suppose we want to add a column Regdate into the table Employee then the Sql statement for adding the column into the table Employee will be given below:-

Alter table Employee Add Regdate datetime null

The above SQL statement will add a nullable column Regdate to the table which have the datatype as datetime.

The insert command after the addition of the new column regdatum will be given below:-

Insert Into employee Values
('VJO','Vivek', 'Johari',28,'244555121','Delhi',getdate(),'01/01/2012')

Select * from Employee

Suppose if we want to delete a column from the table then we have to write the following SQL Statement

Alter Table Employee Drop Column Regdate

This command will drop the column Regdate from the table Employee which we can verify with the help of the select command.

Select * from Employee

from the above picture, it can be easily see that the column Regdate is not a column of the table Employee anymore.

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...