Adding column in middle of the Table

Question

Hi

I would like to know how to add few columns in the middle of the table without truncating the table data

Answer 1

Adding columns to a table won't affect the existing data in a table.

Add each column individually with an ALTER TABLE statement.

Refresh the database in SSMS then use SSMS to move the columns as specified here

https://msdn.microsoft.com/en-gb/library/aa337556.aspx?f=255&MSPPError=-2147217396

Note; if you're adding columns with NOY NULL settings and you're supplying a default value, this will involve SQL Server writing data to all the pages allocated to the Table, consider the impact of this if this is a large table with millions of rows of data.

---

Please click "Mark As Answer" if my post helped. Tony C.

Answer 2

Hello,

You can not directly add columns inbetween existing columns, only at the end. And the order of columns in a table is completly unimportant.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

For Abinito job they need the column to be added in the middle of the Table rather than end of the table.

is it anyway we can take the backup in a temporary table and repopulate in main table retaining the other column data.

Answer 4

Why don't you create a view with the column order you want to have?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Unfortunately, to add a column in the middle of list of columns, you need to take the long way: create a new version of the table, copy data over, migrate indexes, triggers, foreign keys and referencing foreign keys. It's a game that requires a lot of care.

This is a glaring missing feature in SQL Server.

Saying that column order is unimportant as suggested in another post if course complete nonsense.