Post reply

Truncate Table Load and Rename Always Available

  • June 13, 2013 at 3:38 am

    #394957

    Hi Guys,

    I'm creating a ssis package to run hourly but I need the tables to always be available.

    I was going to load the data into a Load table then use sp_rename to rename the table to existing table minimizing the downtime on the table.

    I have a few concerns regarding Indexes and primary key constraints, should I be dropping and recreating prior to using sp_rename?

    Any thoughts or alternatives would be much appreciated.

    Thanks,

    Rich

  • June 14, 2013 at 7:55 am

    #1624122

    When sp_rename is used to change the name of a table, it just change the name. there is no change to the index, constraints, etc.

    BOL:

    You need to aware about :

    Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it.

    [font="Calibri"]Raj[/font]

  • June 14, 2013 at 8:55 am

    #1624161

    Thanks, I have already taken this into consideration and have created the same indexes on both the origanl and load tables.

    I have now implemented this logic and seems to be working very well.

    Thanks for the help.

    Rich

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply