SQL add/drop/update column operation
The statement ALTER TABLE is mainly used to delete, add, or modify the columns into an existing table. It is also used to add many constraints on the current table.
ADD COLUMN is used to add the particular table to the existing one. We can add additional information without creating the whole database again.
SQL add column is used to add column operation into the existing SQL table. We can drop the table and recreate it according to our need. In the production environment, it is destructive as it pertains to the data.
Syntax of ADD COLUMN
The add column operation is used before the table with the help of transact SQL command.
According to the syntax,
Syntax of column definition:
If we want to add multiple columns to the existing table using any single statement, we can use the below syntax:
Many database support ALTER TABLE ADD COLUMN statement.
To add any one column to a table using SQL, we can specify that if we want to change the table structure by the ALTER TABLE command, which is followed by the ADD command in RDBMS.
The syntax for ALTER TABLE Add Column is,
Look at the below example. Assuming our starting point is the Student table created in the CREATE TABLE section:
Example 1: Add one column to the table
If we want to add any column named "Gender." Then, we write:
The result is shown below:
Note: The new column named "Gender" becomes the last column in the Student table.
Example 2: Add multiple columns to the table
It is possible to add multiple columns.
For example, if we want to add a column called "Telephone" and another column called "Email," we should type the following:
Now the table becomes:
The drop column is used to drop the column in the table. It is used to delete the unnecessary columns from the table.
ALTER TABLE- MODIFY
Modify function is used to modify the existing columns in a simple table. Multiple columns can be changed at once.
After running the above query maximum size, of Course, Column is reduced to 15 from 20.