• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

substituting one table by another

Hi there!

Let´s imagine I have a certain dbo.tableOld that must be substituted by a certain dbo.tableNew.  Their are almost identical, the only difference (in terms of fields) is that the old one has a certain nvarchar(3000) field and the new one has the corresponding field as nvarchar(max).  (I had to make this change.)

I would like to do the following procedures:  

01. Drop the dbo.tableOld
02. Rename dbo.tableNew to dbo.tableOld

In other words, I want to put the new one "over" the old one, everything else (views, store procedures, etc) not being disturbed during this change.

Question: can I do as I intend or should I do this in another way?

The most important think is in bold, that is, I do not want to mess up with all database structures that are related to the dbo.tableOld, just make the new one to be the old one, so to speak.

3 Solutions
Randy PooleCommented:
drop table tableold
EXEC sp_rename 'tableNew', 'tableOld'

this should work fine, but I would personally backup the database and restore it as a new database and try it on that one first
Aneesh RetnakaranDatabase AdministratorCommented:
I would rather rename the existing table too

EXEC sp_rename 'table', 'tableOld'
EXEC sp_rename 'tableNew', 'table'

then later if everything looks great, will drop the old table

This should be done during the offpeak hours.
Scott PletcherSenior DBACommented:
Should work fine.  Run these commands:

EXEC sp_rename 'dbo.tableOld', 'tableOld_Original'
EXEC sp_rename 'dbo.tableNew', 'tableOld'

then verify that everything is running OK.  If not, revert the name and try again after correcting :-) .

Personally I would also explicitly refresh any views that use that table, just in case.  Views can be very quirky when something unexpectedly changes on the underlying object(s).  If you want code to do that, just let me know.
fskilnikAuthor Commented:
Excellent suggestions, thank you all.

@Randy: I used your idea in the sense that I tried the renaming suggested by the others in a backup database. I avoided the drop table because, you will certainly agree, renaming it for (say) "tableOld_Original" would be safer.

@Aneesh and Scott: I did as you suggested, and it took me a bit to come back because I checked some views with care. (The table does not relate to crucial sprocs, therefore I am not that worried in terms of the procedures occuring on the new table.)

Everything seems ok!

Thanks a lot!


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now