Learn How to Add Column Using SQL Server ALTER TABLE ADD Column Statement

Introduction

Every time a table is created in SQL Server using T-SQL, you need to specify all the columns for that table with their data type and constraints. But what happens when you suddenly have to add a new column in a table in SQL? After all, dropping a table and starting again is not an option here. The other option is taking the backup and creating it again after dropping the existing table.

But these options are not optimum and cannot be considered good choices for programmers. In SQL, you can create new columns using an ALTER TABLE statement that can also be used to add, modify, or delete columns in the existing table. It can also add or drop various constraints on the existing table.

Before we start with the actual discussion of “how to create a new column SQL to an existing table,” let us first learn how to create a table in SQL Server using a query. To create columns in SQL, you must enhance your SQL skills. Do register yourself for online SQL server training to give a competitive edge to your career.

Let’s dive into an enlightening journey to learn how to create a table and add a column in SQL!

Do keep a pen & paper ready with you to note down all the steps for your future use!

Before knowing how to create a new column SQL, read the steps in SQL server tutorial to create a table in SQL Server Management Studio. Find the steps below.

• In the SQL Server Management Studio, click the ‘New Query’ button.
• Here, you have to type a ‘Create Table’ script.
• Now, click on the ‘Execute’ option from the toolbar.

Let us see a quick example for your reference for creating a table in SQL. Moving ahead, we will discuss how to add a column in SQL using the ALTER statement.

Creating a table in the SQL involves the name of a table, the definition of columns, and its associated data types too.

The create table in SQL server query is used to create a new table. The basic syntax for creating a new table in SQL is given below.

The syntax for SQL create new column will be discussed later!

CREATE TABLE new_table_name 
( first_column first_column_data_type, 
second_column second_column_data_type, 
third_column third_column_data_type, .... 
last_column last_column_data_type );

The “CREATE TABLE” Script

Creating a basic table involves naming the table and defining its columns and each column's data type in data management. Through a Data Management Certification, you can gain essential skillsets. .

Create table status ( statusid int indentity(1,1) not null, 
statusname varchar(50) not null, Datecreated datetime not null constraint 
Df_Status_DateCreated DEFAULT (Getdate()), 
constraint pk_Status Primary Key clustered (statusid) )

So, how does this script work to create a new table and to add new column to table SQL?

It will create a table. It adds three columns and defines data types for each column. It specifies that Status ID is an identity column, and the value of the first column is 1. The value for each subsequent column will automatically increase by 1. It specifies that the value in the Status Name column can have a maximum of 50 characters.

It specifies that NULL values are not allowed for the column. The Date Created Column sets a default value for the current data, and the Status IS field is defined as the primary key. This basic script creates a small table and is relatively easy to understand. You can quickly customize this script to run the whole database with its objects, data, and permission all in one go.

When you create database objects, it is a great practice to see whether an object already exists. It prevents errors and helps manage redundancy or duplicate entries that ultimately help improve a database script's overall performance. Keep in mind that Create table script will work only if the table does not exist in the database. Here is how the script will change in this case.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')
You should add this code to your script at the upper end and rerun the script. You can also replace an existing table too. For this purpose, you should drop the existing table and create a new one. For example:

DROP TABLE IF EXISTS [TaskTracker].[Status]

Add this code at the start of the script and use the same script again. It will first drop the existing table and create a new one per the requirement. Now, you have learned how to create a table and a table script in SQL Server Management Studio. Let's discuss how to generate a script from an existing table to add new column to table SQL.

It is possible to generate a SQL Script from Object Explorer in the SSMS GUI. With this, SQL generates all the SQL code from the table and creates a table. For this purpose, you just have to right-click on the table and create a script. It is not limited to tables; you can also generate Scripts for database objects. Learn more through Janbask Training's comprehensive SQL tutorial for beginners & professionals.

Steps to Create Table in Sql: Example

The HR or senior managers ask you to create a table with EMPLOYEE ID, FIRST AND LAST TIME, GENDER, AND EMAIL ID. How to create a table in SQL in this case? Follow the script below.  

Input Commands
CREATE TABLE Employee(
E_ID INT PRIMARY KEY,
E_Name VARCHAR(25),
E_Gender VARCHAR(1),
E_Email_Id NVARCHAR(20) UNIQUE;

INSERT INTO Employee VALUES (1, 'Samantha', 'M', 'sa.com');
INSERT INTO Employee VALUES (2, 'David', 'M', 'da.com');
INSERT INTO Employee VALUES (3, 'Rachel', 'F', 'ra.com');
INSERT INTO Employee VALUES (4, 'Alice', 'F', 'al.com');

SELECT * FROM Employee;

Output

 

So, how does this script work to create a new table and to create a new column SQL?

The script will-

• create a table. 
• It adds three columns and defines data types for each column. It specifies that Status ID is an identity column, and the value of the first column is 1. 
• The value for each subsequent column will automatically increase by 1. 
• It specifies that values in the Status Name column can have a maximum of 50 characters.
• It specifies that NULL values are not allowed for the column. 
• In the Date Created Column, it sets a default value for the current data and the Status IS field is defined as the primary key. 

So, this is just a basic script that creates a small table and is quite easy to understand. 

You can quickly customize this script to run the whole database with its objects, data, and permission all in one go.

Besides steps to add column in SQL, when you create database objects, it is a wonderful practice to see whether an object already exists or not. It prevents error and helps in managing redundancy or duplicate entries that ultimately helps in improving the overall performance of a database script. 

Please Note: Keep in mind that Create table script will work only if the table does not exist in the database. Here is how the script will change in this case.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')

You should add this code to your script at the upper end and run the script again. You can also replace an existing table too. For this purpose, you should drop the existing table first and create a new one then. For example:

DROP TABLE IF EXISTS [TaskTracker].[Status]

Add this code at the starting of the script and use the same script again. It will first drop the existing table and create a new one as per the requirement. 

Also, Note: It is possible to generate a SQL Script from Object Explorer in the SSMS GUI. With this, SQL generates all the SQL code from the table and creates a table. For this purpose - 

• You just have to right-click on the table and create a script for the same. 
• It is not limited to tables only, but you can generate scripts for database objects as well.

Now, that you know how to create a table and a table script in SQL Server Management Studio, it's time to learn how to add columns in SQL. 

Are you looking for a job as a DBA or Developer? Or are you preparing for an interview or test? Check out the Top 50 DB2 Interview Questions and Answers and crack your next interview with confidence!

SQL ALTER TABLE Statement

In SQL, create new column using the ALTER TABLE statement. This program helps to add column in SQL. You can also modify, or delete columns in the existing table. 

It can also be used to add or drop various constraints on the existing table. Let us see the concept of the ALTER TABLE statement in detail below.

How to Add Columns in SQL?

The Alter Statement is used to add column to SQL table without dropping or removing them. Here is the basic syntax for the SQL add new column.

ALTER TABLE table_name ADD column_name column_definition;

Now, as per the above example of creating an employee data table, your manager suddenly asks you to add ‘ADDRESS’, how to do it? 

Check here! 

Input Values

ALTER TABLE Employee
ADD E_Address NVARCHAR(30),
SELECT * FROM Employee;

Output

 

Develop strong practical skills to become a self-assured pro-SQL! Join the JanBask Training to learn more. 

How to Add Multiple New Columns to the Table?

With the ALTER TABLE statement, it is not necessary to add column in SQL at a particular time, but you are free to add multiple columns too. 

Here is the syntax of how to add multiple new columns to an existing table.

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

For example, you want to add two columns - ‘CONTACT NUMBER’ and 'SALARY' to the employee table. In this case, the above basic syntax can be written as:

ALTER TABLE Employee ADD Contact_Number VARCHAR(50),
Salary VARCHAR(40);

With this syntax, you can add two columns successfully where the limit is set to 50 characters for the ‘CONTACT NUMBER’ column and 40 characters for the ‘SALARY’ column.

The Output will look like -

You may not find any difference in the output as per the above image. So, just add an integer number in the first row of the table as per the above image with the UPDATE statement, and check if it accepts the data. If your column gets modified, it will definitely accept the data. You can then insert it into the row.

UPDATE Employee
SET E_Salary = 50000
WHERE E_ID = 1;
SELECT * FROM Employee;

Output

Now, you will find the updated data modified in the above column as INT. Get updated with each and every update on SQL through the SQL server community.

How to Add Multiple New Columns to the Existing Table?

With the ALTER TABLE statement, adding only one column at a particular time is not necessary, however, you are free to add multiple columns too. Here is the syntax of how to add multiple new columns to an existing table.

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

For example, you want to add two columns “LASTNAME” and “FIRSTNAME” to the employee table. In this case, the above basic syntax can be written as:

ALTER TABLE employees  
ADD last_name VARCHAR(50),      
first_name VARCHAR(40);

With this syntax, you can add two columns successfully where the limit is set 50 characters for the last name column and 40 characters for the first name column.

Take a peek at our guide on SSRS Sub Reports  to find out how to deploy an SSRS report file into the production server and manage it using the report manager tool

How to modify a column in an existing table?

In SQL create new column and modify it with ALTER TABLE. To modify a table in SQL Server, you should use ALTER TABLE statement, and here is the syntax for the same:

ALTER TABLE table_name ALTER COLUMN column_name column_type;

If we take the above example where we need to modify the column ‘SALARY’. Salaries are either in INT or Decimal. Hence, the script in such a case will be -

ALTER TABLE Employee
ALTER COLUMN E_Salary INT;
SELECT * FROM Employee;

The Output will be -

NOTE: Feel free to share your experience of being a SQL developer with us, via the Comment section! And, don’t forget to play our SQL Quiz and enhance your knowledge accordingly.

How to Drop a Column in an Existing Table?

Besides steps to add column to SQL table, you can drop columns too! 

To drop a table in SQL Server, you should use ALTER TABLE statement and here is the syntax for the same:

ALTER TABLE table_name DROP COLUMN column_name;

Let us understand the concept with the help of the above example. It will drop an already added column that is not needed anymore.

ALTER TABLE Employee DROP COLUMN Address;

With the syntax, the ‘ADDRESS’ column will be dropped from the table Employee, and you cannot use it anymore.

Writing a complex SQL query can be difficult as it involves joins across different tables and multiple nested conditions. Check out our guide on what is complex SQL query, how to write it, and break it down into simple steps.

Register for a demo class now to upgrade all your SQL skills now!

How to Rename a column in an existing table?

Besides knowing how to create column SQL, you are now aware of how to rename, modify and drop columns. 

Here, we have a bonus for you! 

How to rename a Table in SQL server? Yes, to rename a table in SQL Server, you cannot use the ALTER TABLE statement, but you can use the sp-rename command for the same. 

Microsoft recommends that you drop a table and create it again so that stored procedures and scripts are not broken. Here is the syntax for the same:

sp_rename 'old_table_name', 'new_table_name';

Let us understand the concept with the help of an example. It will rename a table according to the context as needed.

sp_rename 'Employee', 'Employees’';

Output

Booyah! 

In the above image, the ‘Employee’ table name is changed to ‘Employees’ successfully.

In between, are you planning for certification in SQL server? 

Check Out: How To Become SQL Certified:Boost Your Career & Income With Right Certification

How to Rename an Existing Table?

Besides knowing how to create column SQL, you are now aware of how to rename, modify and drop columns.

Here, we have a bonus for you! 

How to rename a Table in SQL server? Yes, to rename a table in SQL Server, you cannot use the ALTER TABLE statement, but you can use the sp-rename command for the same. 

Microsoft recommends that you drop a table and create it again so that stored procedures and scripts are not broken. Here is the syntax for the same:

sp_rename 'old_table_name', 'new_table_name';

Let us understand the concept with the help of an example. It will rename a table according to the context as needed.

sp_rename 'Employee', 'Employees’';

Output

Booyah! 

In the above image, the ‘Employee’ table name is changed to ‘Employees’ successfully.

In between, are you planning for certification in SQL server? 

What are the Limitations and Restrictions?

In SQL create a new column with the ALTER TABLE statement. The columns are automatically added to the end of a table. To add columns in SQL in a specific order, you should SQL Server Management Studio. However, it is not the recommended database design practice. 

Still, you can use it if ordering columns are necessary for some databases. It is always pleasing to our eyes to add columns in a specific order as you want. 

You need the ALTER permissions to make changes to an existing table. Let us see how SQL Server Management Studio can help you to deal with the problem.

Input

ALTER TABLE Employees
DROP COLUMN E_LName;

Output

• Now, you must add the ‘E_LName’ with the help of GUI and place it by the side of the ‘E_Name’ column.
• In Object-Explorer, right-click on the table to which you want to add new columns in a specific order.
• Click the first blank cell in the ‘Column Name’ column.
• Type the name for the column in the cell. The column name is the required value here.
• In the next step, press the TAB button to jump the DATA TYPE cell and select a data type for the dropdown menu.
• The default value is assigned here if you don’t give any suggestions.

Please Note: It is possible to change the options dialog box under ‘Database Tools.’ Continue defining more properties for the column in the column properties tab.

Please Note: Default properties are defined for each column as soon as it is created. However, you are always free to customize a column in SQL as per the requirement. Once you have added all required columns, Click the Save option in the end.

Here is one more option to change the column positions, if you don’t want to use SQL SERVER Management studio to resolve the issue. When you are unhappy with the current column position, you can use the following syntax to change the column position as you want it.

ALTER TABLE table_name MODIFY column_name column_definition AFTER column_name;

You can also see a practical example for a better understanding of the concept. It is given below for your reference:

mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;

The discussion is incomplete if you are not sure about adding constraints to existing columns. It is possible to achieve this with the ALTER Table statement again. Let us see how to do it.

Also, if you want to gain complete insight into an SQL Developer. Check out how to become SQL developer with a click.

How to add constraints with the ALTER TABLE statement?

Here is one quick example of how to add a UNIQUE constraint to a particular column with an ALTER TABLE statement in SQL.

ALTER TABLE table_name ADD UNIQUE (column_name,...);

Here is one quick example of how to add a PRIMARY KEY constraint to a particular column with an ALTER TABLE statement in SQL.

ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);

Here is one quick example of how to add a NOT NULL constraint to a particular column with an ALTER TABLE statement in SQL.

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Here is one quick example of how to add a CHECK constraint to a particular column with an ALTER TABLE statement in SQL.

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

Here is one quick example of how to add a DROP constraint to a particular column with an ALTER TABLE statement in SQL.

ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

Here is one quick example of how to change the data type of a particular column with an ALTER TABLE statement in SQL.

ALTER TABLE table_name ALTER COLUMN column_name new_data_type;

A data warehouse is a centralized storage facility, which means that all of your data can be kept in one place. This makes it incredibly easy to retrieve and sort through your data, as well as share it with other people. To know more read our guide on Data Warehouse.

Final Thoughts

You can easily add column in SQL using the ALTER TABLE. Our tricks mentioned above are a complete guide on the same along with real-life examples. Now you must note down these steps somewhere and try implementing them on your own. This will be a holistic learning opportunity for you. 

However, ALTER TABLE command will be a Child’s Play if you have some background related to it or if you sharpen up your skills with the right certification. Then all these tricks will be easy to practice in real-life cases. Whether you have those credentials or not, our affordable online SQL server training at JanBask Training along with free career consultation will help you learn from scratch to develop commands on database systems. Join our comprehensive SQL Server Certification to be industry-ready!

Why wait? We are just a call away! Join now and be industry-ready!

For the time being, do share your experience after implementing our tricks in the comment section. 

Also, subscribe to our newsletter to stay tuned to such insightful information! 

All the Best!

FAQs

Q1. What is SQL?

Ans:- The full form of SQL is Structured Query Language, which is used for communicating with the Database. This standard language is used to execute certain tasks like retrieval, updation, insertion, and deletion of data from a database.

Q2. Give details of your online SQL server training course.

Ans:- As you know, information is all around us, and to use that information, it is important to learn SQL fundamentals. SQL makes working with data easy and helps make informed decisions. Through this course, you’ll learn skills to work with data, manage data sets, assess real data, and much more.

Q3. What are the prerequisites for learning SQL?

Ans:-There are no specific prerequisites to learning SQL since it is easy to learn. This query language is similar to the English language. So, anyone can learn and write SQL queries easily. The basic educational qualifications needed are a bachelor's or master's degree in computer science, IT, or any relevant field.

Q4.  What will I learn from your online SQL training course?

Ans:-Through this online training course, you’ll learn SQL and gain a functional knowledge of RDBMS's leading programming languages. It’ll cover crucial topics such as MySQL, SQL Server, in-built SQL functions, joins, stored procedures, and much more. 

Q5. What are the key features of your online SQL training course?

Ans:- Want to dive deep into the SQL Career? Through our SQL training course, learn about SQL career benefits, in-demand skills, average salaries, and tips to crack job interviews. Here are the fey features of our course

• In-depth coverage of SQL basicls
• Coverage of all of the important SQL query tools and commands
• Industry-recognized SQL course completion certificate
• Lifetime access to self-paced learning

Q6. What are the benefits of earning SQL certification?

Ans:- SQL, is highly useful oday as businesses around the world are collecting huge amounts of data for their growth. SQL makes it easy to handle that data and it continuously ranks high in the most in demand tech skills. Learning SQL will definitely add value to your existing array of skills.

Q7. What is the eligibility criteria for SQL training and certification?

Ans:- This course is ideal for freshers, programmers, software developers, software testing professionals, etc. it is also ideal for marketing and salespeople who want to understand their company’s data in a better way.

Q8. What are the system requirements for taking this exam?

You should have a computer with:

• Chrome or Chromium browser
• Strong internet connection
• Webcam
• Microphone, speakers, and headset

Q9. Who will be the trainers?

Ans:- All our SQL trainers are certified and are highly qualified, with several years of experience in working with development technology.

Q10. What does SQL Developer do?

Ans:- SQL developers plan, work and enhance the business level databases, including administration of databases for different companies, government agencies, and educational institutions. Several SQL developers additionally write or gather user needs for database documentation.

FaceBook Twitter Google+ LinkedIn Pinterest Email