Replace Record in Table from A Backup of the Same Database

Posted on 2011-05-05
Last Modified: 2012-06-21
I have a Live Database called LiveDB.
I restored a copy of a backup that was taken of LiveDB two nights ago. The restored database is called LiveDB_restore

Today, by accident, I overwrote one record on a table in the LiveDB. I want to replace that record from the data in LiveDB_restore.

The table name is Loans.
The primary key field in Table Loans is called RecID.
The RecID = "820d0d09"

I would like to accomplish this via a query in SQL Server Management Studio
Please help me.
Question by:nkraemer
    LVL 42

    Expert Comment

    Insert into livedb.SCHEMANAME.TABLENAME
       Select * From livedb_restore.SCHEMANAME.TABLENAME
    where RecID = "820d0d09"

    LVL 38

    Accepted Solution

    Open a query window to livedb.
    Insert into loans
    Select * from where
    RecID = '820d0d09'
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    Here you go:

    INSERT INTO LiveDB.schemaname.Loans (col1, col2, col3)
    SELECT col1, col2, col3
    FROM LiveDB_restore.schemaname.Loans
    where RecID = '820d0d09'

    If it errors out, then you have that RecID already available and you need to update records using

    UPDATE LiveDB_restore.schemaname.Loans
    SET col1 = t2.col1,
    col2 = t2.col2
    FROM LiveDB.schemaname.Loans t1
    JOIN LiveDB_restore.schemaname.Loans t2 on t1.RecID = t2.RecID
    WHERE t2.RecID = '820d0d09'
    LVL 1

    Author Comment

    Having to match all 112 columns to each other will take forever. Is there a simpler way?
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    My way should drop right in. Back it up first of course.
    LVL 1

    Author Comment


    when I try to INSERT I get error

    Viloation of PRIMARY KEY constraint. Cannot insert duplicate key....
    LVL 9

    Expert Comment

    Since you have updated the record (as opposed to deleting it), the only way it will work is you go with the update method that rrjegan17 suggested.

    Since it is a PRIMARY KEY, DO NOT delete the record and try to insert it like others are recommending it.  You'll lose database integrity if you do that.

    LVL 57

    Expert Comment

    by:Raja Jegan R
    >> Having to match all 112 columns to each other will take forever. Is there a simpler way?

    Yes, Generate a INSERT script from SSMS which would have mapped all the 112 columns for you..

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This video discusses moving either the default database or any database to a new volume.
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    745 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

    17 Experts available now in Live!

    Get 1:1 Help Now