SQL SERVER - How To Insert a New Column At a Particular Position in a Table?

I have seen the Following question more frequently in many of the forums like “How to insert a new column at a particular Position in a table? ”

In general, If we add a new column in a table, this column will be added at the end of all the existing columns. In technical aspect, There is no significance to the order of the columns in a table. But for human consumption the column order has significance.

Let us see various methods to insert a new column at a particular position in a table.

Method 1:

1. Let us Consider a table Organization table with columns OrgId, AcctNum and OrgName.

CREATE TABLE Organization (OrgId int,Acctnum varchar(10), OrgName varchar(30))

INSERT INTO Organization
Select 1,'C00001','DEF Corp'
Union All
Select 2,'C00002','GHI PVT Ltd'
Union All
Select 3,'C00003','XYZ Corp'

Now the  output of the table is :

Select * From Organization

Result :

2. Now we want to add a new Column CompanyId between Acctnum and OrgName.

For this, we will Create a new Table Organization_New  with the desired column order.

CREATE TABLE  Organization_New (OrgId int, Acctnum varchar(10), CompId Int Null, OrgName varchar(30))

3. Now Insert values from the existing Old table  Organization to the New Table ‘Organization_New’

Insert Into Organization_New(OrgId,Acctnum,OrgName) Select OrgId,Acctnum,OrgName From Organization

Here the Default values for the new column CompanyId will be Null.

4. Now Drop the old table Organization and Rename 'Organization_New' to 'Organization'.

----Now Drop the old table
Drop Table Organization

---Rename the New table SP_Rename 'Organization_New' , 'Organization'

5. Now you can update the values of the new column CompId using the Update statements.

Update Organization set Compid= 34567 Where Orgid=1
Update Organization set Compid= 34568 Where Orgid=2
Update Organization set Compid= 34569 Where Orgid=3

Finally here is the Organization table with desired Column order. Now If we want to see the table details,

Select * From Organization

Result :

Method 2:

The second method is to add the new column ‘CompId’ at the desired position in the Organization table in the Object Explorer of the SQL Server Management Studio.

For this First select the table in the in the Object Explorer of the SQL Server Management Studio. Right Click on the Table Organization and Select ‘Modify’

    A Table Designer Window will be displayed at the right hand side of the SQL Server Management Studio as shown below.

Now in Order to add a new column CompId between AcctNum and OrgName, Right click on the Column OrgName and you will be seen a Popup window. Now Select the ‘Insert Column’  from the Popup window and Add the new Column ‘CompId’ between AcctNum and OrgName.

Now the Table Designer will be shown as below :

 

Now Save the Changes and click the Refresh button and check the  columns of the table. Now we can see the newly added column CompId under the columns list.