I have two tables, tblCode and tblCodeAlternative

tblCode has about 4100 rows and is used in production.

tblCodeAlternative has about 4200 rows and is in the production database but not used anywhere.

What I need to do is swap the contents of the above database so tblCode becomes tblCodeAlternative and tblCodeAlternative becomes tblCode.

So really what I want to do is swap the contents of the two tables. I have tried to rename tblCode to tblCodeTemp, then rename tblCodeAlternative to tblCode and lastly rename tblCodeTemp to tblCodeAlternative.

However, for some reason this makes some of the application fail. The database itself is not producing any errors, in fact the renaming is surprisingly quick.

I'm not entirely sure why parts of the application is failing. If I revert the renaming then it all works again.

To rule out any mistakes on the database side, is it OK to rename a table the way I have done, or should it be done in another more robust way? I realise that for a few seconds there is no table called tblCode which is used in the application.

  • What errors are being generated? Can you post the DDL of both tables? Seems to me that there is either a column name difference or a datatype difference. – datagod Feb 4 '16 at 17:35
  • @datagod You made me check the column names, datatypes and keys. It seems like I forgot to add a primary key to the alternative table! I will try and correct that. But should I still rename the same way I have done before? Is that safe? – DSF Feb 4 '16 at 17:42
  • 1
    Sure, you can rename, or use schemas to transfer as I do here: sqlperformance.com/2012/08/t-sql-queries/… and here: sqlperformance.com/2013/04/t-sql-queries/… – Aaron Bertrand Feb 4 '16 at 17:52
  • 1
    Double check your permissions too. The permissions will stay with the existing table regardless of name. – Kenneth Fisher Feb 4 '16 at 18:18
  • The three step approach you are using is one I have used successfully in the past. Make sure you do this when there are no other processes using the tables, and you should have no problem. – datagod Feb 4 '16 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.