SQL Server does not give you an option to insert a column at a specific point with a query (unlike MySql) - they are always inserted at the end. The only way to do it is:
1) Create a of new table that has got the required structure
2) Copy the data from the original table into this new table
3) Delete the original table
4) Rename the new table to the original table name
Inserting a column at the end is easy:
ALTER TABLE myTable ADD newColumn INT
To be honest, there is no good reason for inserting a column at a specific column: Unless you specify "all columns" in your SELECT statement (and you shouldn't really do that anyway) you specify the order in which columns are returned, so the order in the DB is irrelevant.