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:


Alter Table Add Multiple Columns Sql Server


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.



Post a Comment

Post a Comment (0)

Previous Post Next Post