substituting one table by another

Posted on 2014-07-15
Last Modified: 2014-07-15
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.

Question by:fskilnik
    LVL 21

    Assisted Solution

    by:Randy Poole
    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
    LVL 75

    Accepted Solution

    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.
    LVL 68

    Assisted Solution

    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.

    Author Closing Comment

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now