Picture this, you have created a large table in a database to manage employee data. The HR and senior managers ask you to include columns such as employee_id, first_name, last_name, gender, email_id, and address. You already successfully created the table. But now, HR comes and says that they want you to add column in SQL to the already created Employee table. What will you do?
There are two options available; you can take backup, drop the table, and create a new one or add another column to the table. The first option is not a good choice as it will consume a lot of time, especially if the table has vast data. Hence, programmers are not likely to take that option leaving the better choice to add another column at disposal. For this, you can use the ALTER TABLE command. This command lets you add, modify, and delete several things including, constraints, views, and columns in SQL.
How to Create an SQL Table?
Here, you will explore the ways to add column in sql to an existing table here. Hence, before starting with anything else, create a table using the CREATE TABLE command and populate it using the INSERT INTO statement. In the end, you will use the SELECT statement to view the table. For this example, you must create a table named ‘Employee’ with E_ID, E_Name, E_Gender, and E_Email_Id columns.
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, 'George', 'M', 'ge.com');
INSERT INTO Employee VALUES (2, 'David', 'M', 'da.com');
INSERT INTO Employee VALUES (3, 'Florina', 'F', 'fl.com');
INSERT INTO Employee VALUES (4, 'Rachel', 'F', 'ra.com');
SELECT * FROM Employee;
Output:
As you can see, the code mentioned above creates the ‘Employee’ table with four columns and four rows. You have set the ‘E_ID’ column as a primary key and the ‘E_Email_Id’ column as unique.
NOTE: You will have to use a Microsoft SQL server throughout.
What is an SQL ALTER TABLE Statement?
As mentioned previously, the ALTER TABLE allows you to modify, delete, and add columns in SQL. It also enables you to do the same with constraints, indexes, and views. But for this article, you will keep the constraints, indexes, and views at bay and concentrate on how to add a column in SQL and work with it using the ALTER TABLE statement.
How to Add a Column in SQL?
To add columns in SQL to an existing table, you can use the ALTER TABLE command and insert the column name and description. The syntax for the same is as follows:
ALTER TABLE table_name
ADD col_name data_type;
Now, use the table and insert a new column, ‘E_LastName,’ to the already existing ‘Employee’ table.
ALTER TABLE Employee
ADD E_LastName VARCHAR(20);
SELECT * FROM Employee;
Output:
As you can see in the output, the above code adds a new column, ‘E_LastName,’ to the table and keeps the value NULL as you haven’t added the values for it to the table yet.
How to Add Columns in SQL (Multiple)?
With the same ALTER TABLE command, you can quickly add multiple columns to an existing table through a single ALTER TABLE command. Here’s the syntax to add columns in SQL.
ALTER TABLE table_name
ADD col_1_name data_type
col_2_name data_type
…
col_n_name data_type;
Now that you know the syntax to add columns in SQL, use it to add two columns, ‘E_Address’ and ‘E_Salary,’ to your already existing ‘Employee’ table.
ALTER TABLE Employee
ADD E_Address NVARCHAR(30),
E_Salary VARCHAR(10);
SELECT * FROM Employee;
Output:
How to Modify a Column in an Existing Table?
The ALTER TABLE command also lets you modify an existing column. The syntax for modifying a column is:
ALTER TABLE table_name
ALTER COLUMN col_name col_description
In the syntax mentioned above, you can mention the modifications in the place of col_desription.
For this example, you will modify the E_Salary column. If you have noticed, this example has used VARCHAR data_type for this column. But a salary can be either INT or decimal. Hence, you will modify the data_type for that column.
ALTER TABLE Employee
ALTER COLUMN E_Salary INT;
SELECT * FROM Employee;
Output:
You might not be able to witness any difference in the above output. But add an integer number to the first row using the UPDATE statement and see if it accepts the data. If the column was modified, it will accept the data and insert it into the row.
UPDATE Employee
SET E_Salary = 50000
WHERE E_ID = 1;
SELECT * FROM Employee;
Output:
As you can see, it updated the data as it modified the column to be of INT data type. Now, try to insert VARCHAR data to see the results.
UPDATE Employee
SET E_Salary = 'Hi'
WHERE E_ID = 2;
SELECT * FROM Employee;
Output:
As you can see, the query failed as the server had to convert the VARCHAR data to INT to add in the row, but it failed.
How to Drop a Column in SQL?
Like how you add columns in SQL, you can also drop it using the ALTER TABLE command. The syntax for dropping a column is as follows:
ALTER TABLE table_name
DROP COLUMN col_name;
This will drop an already existing table from the table. Now, use it in your ‘Employee’ table to drop the ‘E_Address’ column.
ALTER TABLE Employee
DROP COLUMN E_Address;
SELECT * FROM Employee;
Output:
How to Rename a Column From an Already Existing Table?
Once you add columns in SQL, you can also rename them. But you cannot do this with the ALTER TABLE command. Instead, you have to use the sp_rename command for this. The syntax to rename a column in SQL is:
sp_rename 'table_name.old_col_name', 'new_col_name', 'COLUMN';
Now that you know the syntax, it’s time to apply it and rename the ‘E_LastName’ column from your ‘Employee’ table to ‘E_LName.’
sp_rename 'Employee.E_LastName', 'E_LName', 'COLUMN'
SELECT * FROM Employee;
Output:
Although the sp_rename command helps rename the column, Microsoft recommends dropping the column and re-adding it with a new name to prevent breaking the stored procedures and scripts.
How to Rename a Table?
Similar to a column, you can also rename a table. However, like columns, Microsoft also recommends dropping and recreating a table. Regardless, the syntax of renaming a table is given below.
sp_rename 'old_table_name', 'new_table_name';
Now, use the syntax and rename your ‘Employee’ table to ‘Employees.’ You must then use the SELECT statement with the table name as ‘Employees’ to confirm the name change.
sp_rename 'Employee', 'Employees';
SELECT * FROM Employees;
Output:
What are the Limitations and Restrictions to Add Columns in SQL?
When you use the ALTER TABLE command to add columns in SQL, it will, by default, add the columns to the end of the table. This is a significant limitation as it is always good to see the columns of a table in the preferred ordering. However, it is easy to overcome this limitation by using the SQL Server Management Studio. If you have ALTER permissions in the server studio, follow the steps below to change the ordering of a column. For this example, you will first drop the ‘E_LName’ column and then add it with the GUI.
ALTER TABLE Employees
DROP COLUMN E_LName;
Output:
Now, add the ‘E_LName’ using the GUI and place it beside the ‘E_Name’ column.
- Find the table in the object explorer and expand it, click on columns, and select New Column.
- Add the column name, data type, and other properties you want in the column.
- Double click on the arrow on the extreme left and drag the new column wherever you want.
However, this will only work if it has permitted you to delete and re-create the table. That’s because despite just creating a new column, the server will anyhow delete and recreate the table. Thus, ordering columns is highly recommended to be done while creating the table itself.
Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!
Conclusion
In this article, you learned everything about how to add columns in SQL, along with a simple example. You have also seen how to modify, rename, and drop a column in an existing table using the ALTER TABLE command. You can now use the ALTER TABLE command to add columns to your tables in your database.
Try different things to get a better understanding of the concept and how it all works. However, if you want to get a complete grasp on this and other fundamental concepts, take Simplilearn’s SQL Certification Training Course. The course comes with unlimited access to self-paced learning materials and hands-on applied learning techniques for different SQL servers. To put it simply, the course can help you excel in database management and build a successful career in the field.
Have any questions for us? Leave them in the comments section of this article, and our experts will get back to you on the same, as soon as possible!