Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: Replacing a Messed up table with Backup (SQL Server 2005)

    SQL server 2005
    kind of new at sql programming

    How would I go about replacing all the data in a table with a backup table. In short I inserted like 5000 entries into my main table that shouldn't have been inserted.....

    Table names
    table
    table_backup

    I know I can drop and recreate the table(key issues maybe)...but Im a little hazy in how I would insert all the data from the backup to the original

    or maybe there is an option in sql server to restore a table to another table?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Any way you go, you will need to address foreign and primary key issues. If you can easily identify the bad records in the table, go ahead and delete them, and you are done.

    if you need to insert records from another table in the database, that can be as easy as
    Code:
    insert into table
    select * from table_backup

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First you need to have a backup that is before the data got modified

    Do you have that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2009
    Posts
    21
    yeah I have two tables in the database.

    Person
    Person_backup

    with person_backup identical to person before the inserted rows.

    and I think it might be easier to learn how to do the sql code instead of going through 5000 entries....

    EDIT:
    i think i can go

    DELETE FROM Person
    insert into Person
    select * from Person_backup

    ?
    the insert statement doesn't sit well with me and I am unsure if that is how to do it
    Last edited by fff398; 10-15-09 at 14:27.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Try it with some other tables first, until you are confident.

  6. #6
    Join Date
    Sep 2009
    Posts
    21
    okay it looks like it worked. thanks for the help.

    The insert threw me off with the select statement there and since im starting out, ive always used insert to just insert a value.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    hmmmmm

    Also look up sp_rename in BOL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounded like he might have had foreign keys pointing at the old table. I don't think those get redirected for sp_rename, but I have not tried it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •