In sql Server you cant add a column to mid of a table .If you add a new column to existing table then it will append end of the table. But in case of adding a column to to mid of the table you can do it like as follows;
---Old table
Create Table A
(col1 varchar(50) null,
col2 varchar(50) null)
--Add new column col3
Begin Transaction
Create Table B
(
col1 varchar(50) null,
col3 varchar(50) null, -- NEW COLUMN IN MIDDLE (NULLABLE)
col2 varchar(50) null
)
go
if exists(select * from A)
exec('insert into B (col1, col2)
select col1, col2 from A with (holdlock tablockx)')
go
drop table A
go
execute sp_rename 'B', 'A', 'object'
go
commit
- Create a new table with the required columns,
- Copy the data from the old table into the new table
- Delete the old table
- Then rename the new table.
---Old table
Create Table A
(col1 varchar(50) null,
col2 varchar(50) null)
--Add new column col3
Begin Transaction
Create Table B
(
col1 varchar(50) null,
col3 varchar(50) null, -- NEW COLUMN IN MIDDLE (NULLABLE)
col2 varchar(50) null
)
go
if exists(select * from A)
exec('insert into B (col1, col2)
select col1, col2 from A with (holdlock tablockx)')
go
drop table A
go
execute sp_rename 'B', 'A', 'object'
go
commit
Comments