Replacing an existing table

  • Say I have a table, lets call it "Table1", and I discover that I am missing some key info, i want to reimport it from an external source. But I would prefer to keep the original table, at least for a week or so.

    Now the question is, what is the best way to replace the table without messing up any relationships and stored procedures?

    If I import the table again into a new table and call it "Table1b", and then rename the original table "table1a", and then rename "table1b" to "table1", will that be ok?

    Here are the steps:

    Step 1: Import the table again as "Table1b"

    Step 2: Rename Table1 as "Table1a"

    Step 3: Rename the newly imported table (Table1b) as "Table1", thus replacing the original table.

    I would imagine that the stored procedures would be ok, but maybe the relationships would need to be re-established?

  • I'm pretty sure that will mess up your relationships since they use the table ID, not the name.

    The stored procedures use the name so they should be ok.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • You would need to rebuild the relationships. They would remain pointing to the first table regardless of the name of the table.

    I'd generate the drop and create scripts before doing the new import. Drop the constraints on the original table, rename both tables and then run the create scripts against the new table to re-establish the relationships.

  • You could also look into importing the fresh data into a new table and updating table1 based on table2.

    If that's not possible, then follow Jack's advice.

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

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