Add a Column to Specific Position in Mssql Server

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.

Add a column to specific position in MSSQL Server

You would need to drop the table and recreate it with the columns in the correct order. If you make the table changes in SSMS then it can generate a change script for you which you could then use to deploy the change to a production server.

How to insert columns at a specific position in existing table?

ALTER TABLE by default adds new columns at the end of the table. Use the AFTER directive to place it in a certain position within the table:

ALTER table table_name
    Add column column_name57 integer AFTER column_name56

From mysql doc

To add a column at a specific position within a table row, use FIRST or AFTERcol_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

I googled for this for PostgreSQL but it seems to be impossible.

How to add a column in TSQL after a specific column?

Unfortunately you can't.

If you really want them in that order you'll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.

how to add a column at a specific position in a table?

Not very easy in sql server..

I think the best way is to add the column at the end of the table and create a view with desired order of columns..

another option..

put all the data in a temp table and recreate the actual table with correct column order, then insert the data back to the actual table from temp table

Inserting column between other columns in SQL Server using script

There isn't another way to insert a column in a SQL Server table "in between" existing columns - you need to build a temp table and rebuild the old table. That said, column order shouldn't matter - are you sure that the column needs to be inserted in order?

Likely your best bet is to just use the GUI, script it out, and then change the constraint name to something reasonable within the script. You're right that the numerical constraint name isn't ideal, and it's not a best practice to allow SQL Server to determine your object names.

Adding column at specific location vs at the end of data table

If you're using SSMS, you should know that under the hood it is creating a new table with new columns then inserting the data from the original table, redoing all the keys and constraints, dropping the old table and then renaming the new table to use the old table's name.

It is probably better practice to add the row at the end and you can use then T-SQL in an order you want for retrieval.

Another note, if you're not locking the db or don't put it in single user mode it will take longer to run.

tl;dr - do not add column at specific position unless you have time to waste

SQL Server -- Any way to add a column and make it first column in table?

According to Microsoft you can do this only using SQL Server Management Studio.
Check this

---

Related Topics