Add column to mid of table

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;

• 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.

Sample Code
---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