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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.