TSQL ADD Column

Introduction  to TSQL ADD Column

T-SQL ADD column is defined as, the ADD column is the clause of ALTER TABLE statement that can be utilized to add more than one column surviving table, when we utilize the ALTER TABLE statement while adding column then we can see that selected columns are impulsively added at the end of the table, a column of a table is a set of units that carries text or numbers, all columns can reserve a value for every row of the table, T-SQL can add the column as per the requirement in which prior to adding a column, we have to take ALTER permission before adding a column.

Overview of TSQL ADD Column

• We can able to use the ‘ALTER TABLE ADD COLUMN’ statement for adding the new column in the table,

ALTER TABLE table_name ADD [column] column_definition;

So, as per the above statement at first, we have to define a table to which we want to add the column and then we have to define the column definition after adding the column.

• Now let us see is the syntax for the column definition,

column_name data_type constraint;

• If we need to add various columns in the surviving table with the help of a single statement then the following syntax has been utilized,

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,…..;

How to add new column T-SQL?

• Let us see how to add one column to a table, so for that make sure we have a surviving table and we will proceed to add a column into a table, if we have a table name ‘Employee’ and we need to add a column ‘Emp_Lname’,

Syntax will be,

ALTER TABLE table_name
ADD column_name datatype;

For example,

ALTER TABLE Employee
ADD Emp_Lname varchar (25);

• Now, how to add more than one column in a surviving table:

Syntax,

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

For example:

“ALTER TABLE Employee
ADD (Emp_name char (50),
City char (35));”

In this example, we have added two columns in a table such as ‘Emp_name’ and ‘City’, in this way we can able add multiple columns to a table.

T-SQL ADD column statement

TSQL ADD Column is the clause that can be used with the ALTER TABLE statement so that we can able to describe the table in to which column we want to add into it, and also we can able to define definition after the ADD COLUMN clause, let us see how to ADD column statement in T-SQL in which ALTER TABLE statement has been utilized to add a column, to modify a column, to dropping a column or table, to rename a column or table in a database, so we can say that “ALTER TABLE” statement has been useful when we want to add, modify, drop or delete, rename, columns of a table in a database.

• ALTER TABLE statement to add a column in a table:

Syntax:

ALTER TABLE table_name
ADD column_name column_definition;

For example,

ALTER TABLE Employee ADD Emp_Name char(50);

In this example, we have added a column Emp_Name in an “Employee” table with the help of ALTER TABLE statement.

• ALTER TABLE statement to modify a column in a table:

Syntax,

ALTER TABLE table_name
ALTER COLUMN column_name column_type;

For example:

ALTER TABLE Employee
ALTER COLUMN Emp_Name VARCHAR (100) NOT NULL;

In this example, we want to modify the column Emp_Name of an ‘Employee’ table so that its data type of it can be modified and that cannot be null.

• ALTER TABLE statement to drop a column of a table:

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

For example:

ALTER TABLE Employee
DROP COLUMN Emp_Lname;

In this example, we have used the DROP COLUMN statement with ALTER TABLE so it can drop or delete the “EMP_name” column from the ‘Employee’ table.

• ALTER TABLE statement to rename a column or table:

Syntax:

sp_rename 'old_table_name', 'new_table_name';

For example:

To rename a table in T-SQL we have to use the ‘sp_rename’ statement,

sp_rename 'Employee', 'Emp';

In this example, we have used the ‘sp_rename’ to rename the table name as it will rename the table name ‘Employee’ to ‘Emp’.

T-SQL ADD column database examples

Example 1:

Let us see an example that how to add more than one column in a table, for that first we have to create a table by using the below statement, in which we have created a ‘Customer’ table having fields ‘First_Name’, ‘Last_Name’, and ‘Email’ with their datatype, so we can use below syntax for creating a table.

CREATE TABLE Customer (
id INT PRIMARY KEY,
First_Name VARCHAR (100) NOT NULL,
Last_Name VARCHAR (100) NOT NULL,
Email VARCHAR (255) NOT NULL UNIQUE
);

• If we want to add a column “Contact” to the “Customer” table then the below syntax has been utilized,

ALTER TABLE Customer ADD COLUMN Contact VARCHAR (50);

• If we want to add three columns such as, “Address”, “DOB”, and “Linkedin account” in the ‘Customer’ table then we can able add them by using the below query,

ALTER TABLE Customer
ADD COLUMN Address VARCHAR (255),
ADD COLUMN DOB,
ADD COLUMN Linkedin_Account VARCHAR (255);

Example 2:

Let us see an example of how to modify the column of a surviving table using ALTER TABLE statement,

Syntax,

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

• So, if we want to modify the column “Last_Name” of the “Customer” table for a given data type as VARCHAR (75) with NOT NULL in which the value of it cannot be null by using the below syntax,

ALTER TABLE Customer
ALTER COLUMN Last_Name VARCHAR (75) NOT NULL;

In this example, we try to modify the data-type of a ‘Last_Name’ column so by using the above query.

Conclusion – TSQL ADD Column

In this article we conclude that ADD column is the clause of the ALTER TABLE that can be used for adding one or more columns in the table, we have also seen how to add column statements and what are they, and examples of adding the columns in the database.

Recommended Articles

This is a guide to TSQL ADD Column. Here we discuss the introduction, overviews, How to add new column T-SQL, examples with code implementation. You may also have a look at the following articles to learn more –

1. PL/SQL varray
2. SQL WAITFOR
3. PL/SQL LIKE
4. PL/SQL UNION