04
JunFOMO ALERT : FLAT 10% OFF * on ANY COURSE & 25% OFF on TWO COURSES
FLAT10
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 column 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 they cannot be considered good choices for programmers. In SQL create new columns using an ALTER TABLE statement in SQL that is used to add, modify, or delete columns in the existing table. It can also be used to add or drop various constraints on the existing table.
Before we start with the actual discussion “how to create new column sql to an existing table,” let us first learn how to create a table in SQL Server using a query.
Let’s dive into an enlightening journey to know about how to create a table and how to add column in SQL!
Do keep a pen & paper ready with you to note down all the steps for your future use!
Check: SQL Career Path - Step By Step Microsoft SQL Server Career Guide
Before knowing how to create column SQL, do read on steps to create a table in SQL Server Management Studio. Find the steps below.
Let us see a quick example for your reference for creating a table in SQL. Moving ahead, we will discuss how to add column 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 Query in SQL 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 );
Here is a SQL script for the above example in SQL Server Management Studio. You can use Studio as well to create a new table and add column to SQL table. The CREATE TABLE script is given below.
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 value 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.
Read: Difference Between SQLite and MySQL
When you create database objects, it is a wonderful practice to see whether an object already exists or not. It prevents the error and helps in managing redundancy or duplicate entries that ultimately helps in improving the overall performance of a database script. 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:
Read: Job Roles and Responsibilities of a SQL Server Developer you Need to Know
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. Now, you have learned how to create a table and a table script in SQL Server Management Studio. Moving ahead, let us discuss how to generate a script from an existing table to add SQL new column.
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.
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 -
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.
Read: What is SQL Subquery? Types of Subqueries in SQL
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:
Read Job Roles and Responsibilities of a SQL Server Developer you Need to Know
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 -
Now, you know how to create a table and a table script in SQL Server Management Studio, it's time to learn how to add column in sql.
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.
Read: Different Type Of SQL Functions And Their Uses
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
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.
With the ALTER TABLE statement, it is not necessary to add only one column 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 “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.
Read: A Complete Guide of SSRS Tutorial for Beginner
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 -
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:
Read: How to Compare MongoDB and DynamoDB?
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.
Register for a demo class now to upgrade all your SQL skills now!
SQL Server Training & Certification
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
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
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
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.
Read: Brief Introduction To Different SQL Server Operators
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 not complete if you are not sure of adding constraints to existing columns. It is possible to achieve this with the ALTER Table statement again. Let us see how to do it.
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.
Read: What Is A Data Warehouse, And Why You Need It?
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;
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 a command on database systems.
Why wait? We are just a call away!
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!
Read: What is Composite Key in SQL? Composite Primary & Foreign Key Explain with Example
Through market research and a deep understanding of products and services, Jyotika has been translating complex product information into simple, polished, and engaging content for Janbask Training.
AWS
DevOps
Data Science
Hadoop
Salesforce
QA
Business Analyst
MS SQL Server
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Trending Posts
What is SFDC? How is it Helping? Why is it a Better Career Choice?
38.7k
Top 51 Core Java Interview Questions and Answers for Freshers & Experienced Developers
36.1k
Add Column in SQL: A Complete Never-Before Guide for You in 2022
29.5k
Top 110+ Manual Testing Interview Questions & Answers for Freshers & Experienced
24.4k
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Jax Williams
Hi! This blog is very informative. Can we have some more information about careers in SQL database?
JanbaskTraining
Hey, thanks man for your interest! For further information, you can contact us at https://www.janbasktraining.com/contact-us
Amari Jones
I am planning to go for a SQL server certification, can I get some more information on the same?
JanbaskTraining
Hey, thanks a ton for your comment. We feel that you connect with our experts at https://www.janbasktraining.com/contact-us
Zane Brown
This information about the add column in SQL is enlightening. I was not able to achieve this earlier. But after reading this guide, I implemented, and got success! Thanks a ton JanBask!
JanbaskTraining
Hey thank you so much. We are grateful that our blog has been a help to you! Do connect us for any further enquiry at https://www.janbasktraining.com/contact-us
Emilio Davis
Hey, the last part of the blog is quite beneficial as I often wondered how to rename a table in SQL. Thanks JanBask Training!
JanbaskTraining
Hey, this has made our day! Thanks a ton that you liked our blog! We request you to connect with us to know more such enlightening information about qa testing at https://www.janbasktraining.com/contact-us
Knox Miller
Hi, the blog is very insightful. However, I am also looking for some salary related information in SQL.
JanbaskTraining
Thank you for showing interest in us. We will request you to reach out to our education experts at https://www.janbasktraining.com/contact-us