How Do I Add Column to Middle of Table

[grnzbra]

A column was accidentally deleted from a table and needs to be added back in the same place that it was originally. Using Design from the Object Explorer for the table in question, I can easily add the column wherever I want in the table. However, when I go to leave, I get a message that says I can't save it and that I need to recreate the table. This I can do and can load the new table, inserting NULL into the new fields. Now, however I cannot delete the original table because of constraints and I cannot delete the records for the same reason. I have also tried switching the names of the two tables but cannot delete the original table (which now has the new name), I've also tried to delete the constraints from the original table add the back to the new table (which now has the name of the old table).

The error message, rather than saying, "Hey stupid! You can't do that", gives me the impression that there is a way around the problem. Also, the choices for response are "Cancel" which just closes the window and leaves everything as it was or to save the table to text, which just creates a file with the name of the original table.

How can I re-create the table in order to have the original table with the missing field where it was originally located?

 

[Andrzejek]

In Oracle, you can add a field (at the end of the table) and then 'reposition' / moving this field where you want it by [tt]rebuilding[/tt] a table.
I hope you can do the same in SQL Server

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Wouldn't that be a jopb for recovering the old table? Using the transaction log to roll back the database to a state where the table had the missing column.

Chriss

 

[Chris Miller]

This is describing how to restore to a point in time:

https://learn.microsoft.com/en-us/s...ime-full-recovery-model?view=sql-server-ver16

I recommend this, even when you wsay but then all other data since that column was dropped is gone. Well, there are ways to restore into a new database to then be able to only move the one table back into the production data, but also:

What would you even do, if you manage to add the column where it was? The data that was deleted with the dropped column won't fill back in automatically.

Chriss

 

[BobSuruncle]

If you're using SQL Server Management Studio, then go to Tools | Options | Designers and turn off the checkbox for "Prevent saving changes that require table re-creation"

Bob Suruncle

Bob Suruncle