Inserting New Columns in the Middle of a Table

Inserting new columns in the middle of a table?

By default, columns are only added at the end.

To insert a column in the middle, you have to drop and recreate the table and all related objects (constraints, indices, defaults, relationships, etc).

Several tools do this for you, and depending on the size of the table, this may be an intensive operation.

You may also consider creating views on the table that display columns in the order of preferrence (overriding the actual order in the table).

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.

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.

MySQL Workbench 6.3: how should I insert a new column into a specific position (in the middle of the table)

In MySQL Workbench you can open the table editor (via the context menu in the schema tree -> Alter table...). On the columns tab you see all currently defined columns. You can add new ones, remove unneeded ones and drag them into the order you want. Then apply the changes. The upcoming wizard with allow you to review of the code which will be sent to the server. Existing data should not be touched (unless you drop a column with data), but it's certainly safer to keep a backup around in case something goes wrong.

Data loss warning adding column in the middle of a table

Up front disclosure that I work for Red Gate on SQL Source Control.

That change will need to re-create a table. By default SSMS won't let you save that change. However that option must have been disabled in SSMS. It's under Tools->Options->Designers->Table and Database Designers->Prevent saving changes that require a table re-creating.

Given that feature is disabled SQL Source Control has then picked that up as a potential data loss situation, and prompted to see if you want to add a migration script.

If other developers within your team pull this change in through a get latest, then SQL Source Control will let them about any potential data loss with more details, depending on the current state of their local database. If the only change is adding columns to an existing table then this will not drop the data in columns that are unchanged.

If you are deploying to another DB (e.g. staging/UAT/prod) and you have SQL Compare you can use that to see exactly what will be applied to a DB if you try and run this against another non-local database. Choose the create deployment script option and you can sanity check the SQL before running.

As you say adding the column to the end of the table will avoid the need for the rebuild, so is probably the simplest way to avoid this if you don't need to worry about where the column is.

Alternatively you can add a migration script to:

1. Create a new table with the new structure using a temp name
2. Copy the existing data to the temp table
3. Drop the existing table
4. Rename the new temp table to the original name

You mention Migrations v2, the beta feature that changes how migrations work in order to better support branching and merging and DVCS systems. See http://www.red-gate.com/migrations

Version 1 migration scripts will need some modifications in order to be converted to a v2 migration script. It's a fairly trivial change. We're working on documenting this at the moment, and please reach out to us on the Google Group if you'd like more information on this change. https://groups.google.com/forum/#!forum/red-gate-migrations

SQL Server 2008 : Cannot Insert new column in the middle position and change data type

In SSMS Tools -> Options -> Designers you would need to uncheck the option "Prevent Saving Changes that require table re-creation" to allow you to do this in SSMS.

This will rebuild the table and so generally isn't worth the hassle if the table is at all large and will make deployment to production trickier.

If there are columns which logically you would prefer to have next to each other to make writing queries easier you can create a View with the desired column order.

Adding column between two other columns in SQL server

The simple answer is no. Is there a reason why column order is important to you?

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.

---

Related Topics