Sql-server – Replacing a table with new version

database-designsql-server-2008

I am a programmer changing the way a process works, and will be using a different table to do this. The process is just sending emails from a web application. Currently the database scripts run during a batch each night and create entries in a table that contain the subject, and the entire body of the email.
I am moving to a process where only the variable elements will be held in the table.

I am just wondering what the accepted, or most common strategy is for doing this. I've spoken to a few of our database people and they suggested to create an entirely new table and use this going forward.

I am fine with doing it this way, and was wondering if it's a good idea to alter the current table's name, to something like EmailTableOld and create my new table with the original name EmailTable so I don't have to create additional permission scripts and set up new synonyms while removing the old ones.

Is this a good idea? I have identified everything that refers to the table and there are only a few things mostly security granting scripts, and the scripts that actually create email content. The table does not reference or is referenced by any other tables currently. The entire contents of the old table must be kept.

Any suggestions etc are welcome

Best Answer

sp_rename or swapping schemas are the typical ways to accomplish this. Neither really saves you anything about permissions scripts because whether you renamed the new object or swapped it into the dbo schema, it still needs to have permissions applied to match the old version of the table.

For a long time I've preferred swapping schemas simply because I used to do this in an automated job and the "Caution: renaming objects blah blah" warning messages were filling the SQL Server Agent logs. That message is not suppressible but the ALTER SCHEMA...TRANSFER statement does not raise such a message. I have asked for a way to suppress this warning but it was rejected.

(Of course another avenue to consider is to archive the current data and alter the current table to meet the new requirements - but I don't know if that's something you haven't considered, or if it has and there's a reason you can't do that.)

Related Question