Add a new table column to specific ordinal position in Microsoft SQL Server

You have to create a temp table that mirrors the original table's schema but with the column order that you want, then copy the contents of the original to temp. Delete the original and rename the temp.

This is what SQL Management Studio does behind the scenes.

With a schema sync tool, you can generate these scripts automatically.

go into SQL Server management Studio, and "design" an existing table. Insert a column in the middle, right click in an empty area and select Generate Change Script...

Now look at the script it creates. it will basically create a temp table with the proper column order, insert the data from the original table, drop the original table, and rename the temp table. This is probably what you'll need to do.

You may also need to uncheck this option to allow creation of change scripts

The answer is yes, it is technically possible, but you will have a headache doing so and it will take a long time to execute and set up.

One: Create/Copy/Drop/Rename

This is actually what SQL Server is doing in the graphical interface: here's an example of the script it is generating and executing when you click the 'save' button after adding a new column to the beginning of a table.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONGOCREATE TABLE dbo.Tmp_SomeTable    (    MyNewColumn int NOT NULL,    OriginalIntColumn int NULL,    OriginalVarcharColumn varchar(100) NULL    )  ON [PRIMARY]     TEXTIMAGE_ON [PRIMARY]GOALTER TABLE dbo.Tmp_SomeTable SET (LOCK_ESCALATION = TABLE)GOSET IDENTITY_INSERT dbo.Tmp_SomeTable ONGOIF EXISTS(SELECT * FROM dbo.SomeTable)     EXEC('INSERT INTO dbo.Tmp_SomeTable (OriginalIntColumn, OriginalVarcharColumn FROM dbo.SomeTable WITH (HOLDLOCK TABLOCKX)')GOSET IDENTITY_INSERT dbo.Tmp_SomeTable OFFGODROP TABLE dbo.SomeTableGOEXECUTE sp_rename N'dbo.Tmp_SomeTable', N'SomeTable', 'OBJECT' GOGOCOMMIT

Two: ADD COLUMN / UPDATE / DROP COLUMN / RENAME

This method basically involves creating a copy of any existing columns that you want to add to the 'right' of your new column, transferring the data to the new column, then dropping the originals and renaming the new ones. This will play havoc with any indexes or constraints you have, since you have to repoint them. It's technically possible, but again time-consuming both in terms of development and execution.

CREATE TABLE MyTest (a int, b int, d int, e int)INSERT INTO MyTest (a,b,d,e) VALUES(1,2,4,5)SELECT * FROM MyTest -- your current tableALTER TABLE MyTest ADD c int -- add a new columnALTER TABLE MyTest ADD d_new int -- create copies of the existing columns you want to moveALTER TABLE MyTest ADD e_new intUPDATE MyTest SET d_new = d, e_new = e -- transfer data to the new columnsALTER TABLE MyTest DROP COLUMN d -- remove the originalsALTER TABLE MyTest DROP COLUMN eEXEC SP_RENAME 'MyTest.d_new', 'd'; -- rename the new columnsEXEC SP_RENAME 'MyTest.e_new', 'e';SELECT * FROM MyTest DROP TABLE MyTest -- clean up the sample

Three: Live with it

This mightily offends my sense of order ... but sometimes, it just isn't worth reshuffling.