Alter Table Add Multiple Columns In Sql Server

Alter Table Add Multiple Columns SQL Server: In this tutorial, we will see how we can alter a table and add a column to it by the SQL query. 

In SQL Server, you can add multiple columns to a table by using the ALTER TABLE statement along with the ADD keyword. Here's an example of how you would add two columns (Pincode and Phone_No) to a table named student:

Sometimes we wanted to add an extra column after the creation of the table. SQL Server provides the query to add a new column in the old table with the desired data type.

Alter table statement is used to modify, add and drop a column from the table.

Let's see how we can do it.

Add a column in the table

alter table student add Std_Address varchar(500)

Add multiple columns to the table

alter table student add PinCode varchar(6),
Phone_No varchar(20)  

Alter column datatype

alter table student alter column PinCode varchar(8)

You Can Also Use Dynamic SQL to add the column

DECLARE @sql AS NVARCHAR(MAX) = ''
SELECT @sql = @sql + 'ALTER TABLE
mytable ADD '+ column_name +' '+ data_type + ';'
FROM information_schema.columns 
WHERE table_name = 'mytable' and column_name not in ('existing_column1','existing_column2')
EXEC sp_executesql @sql

Rename column name using SQL query

Syntex

EXEC sp_rename 'old_name', 'new_name' ['object_type'];

Example

EXEC sp_rename 'student.PinCode', 'Pin_Code', 'COLUMN';

In this tutorial, we have learned how we can alter the table to add multiple columns and rename the column name.