Friday, April 11, 2014

TSQL Tutorial : ALTER TABLE statement


The TSQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax
To add a column in a table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

Example
Let's look at a TSQL ALTER TABLE example that adds a column in our earlier table named as Customer

Example:

ALTER TABLE Customer
  ADD Customer_PhNo varchar(50);

This TSQL ALTER TABLE example will add a column called Customer_PhNo to the customer  table.

Add multiple columns in table
Syntax
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

Example
Let's look at  TSQL ALTER TABLE example that adds more than one column.
For example:

ALTER TABLE supplier
  ADD (customer_city varchar(50),
       Region varchar(45));

This TSQL ALTER TABLE example will add two columns, Customer_city as a varchar(50) field and region as a varchar(45) field to the supplier table.

Modify column in table

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

ALTER TABLE table_name
  MODIFY column_name column_type;

Example
Let's look at TSQL ALTER TABLE example that modifies a column.
For example:

ALTER TABLE Customer
  MODIFY Customer_name varchar(100) not null;

This TSQL ALTER TABLE example will modify the column called Customer_name to be a data type of varchar(100) and not null constraints.

Modify multiple columns in table
Syntax
To modify multiple columns in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

Example
Let's look at TSQL ALTER TABLE example that modifies more than one column.
For example:

ALTER TABLE Customer
  MODIFY (customer_name varchar(100) not null,
          city varchar(75));

This TSQL ALTER TABLE example will modify both the customer_name and city columns.
Drop column in table

Syntax
To drop a column in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example
Let's look at TSQL ALTER TABLE example that drops (ie: deletes) a column from a table.
For example:

ALTER TABLE Customer
  DROP COLUMN customer_name;

This TSQL ALTER TABLE example will drop the column called customer_name from the table called supplier.
Rename column in table

Syntax
To rename a column in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME COLUMN old_name to new_name;

Example
Let's look at TSQL ALTER TABLE example that renames a column in a table.
For example:

ALTER TABLE customer
  RENAME COLUMN customer_name to custname;

This TSQL ALTER TABLE example will rename the column called customer_name to custname.

Rename table

Syntax
To rename a table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name
  RENAME TO new_table_name;

Example
Let's look at TSQL ALTER TABLE example that renames a table.
For example:

ALTER TABLE customer
  RENAME TO client;

This TSQL ALTER TABLE example will rename the customer table to client

No comments:

Post a Comment