Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fastest way to move data between two databases Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 4:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:13 AM
Points: 27, Visits: 90
I have a table on DatabaseA. This table "inadvertently" had about 1.8 million records deleted about a month ago. I only noticed it yesterday. I have requested our DBA to restore the data from a backup. He is going to restore all of DatabaseA backup from the day before the deletion onto the same server as DatabaseB. He told me that once it is restored, I will need to move the recovered table records to the live table on DatabaseA. Now, of course the table on the live database has accumulated more records since this happened, so the two (old table and new table) will need to be combined. I know of a few different ways to do this, but I'm wondering what would be the fastest, given the volume of records to move. I have considered the following:

1. On DatabaseA, run the data import wizard, selecting the recovered table and appending all the records to the existing table.

2. Insert a chunk of records from the recovered table to the existing one on a schedule.

3. Insert the new records on the live database into the recovered table (there are only about 10,000) and then somehow replace the live table with the (now full) table from the backup.

4. Tell the DBA that this is his job, I'm just a programmer!!!

Any help is appreciated. Thanks!
Post #1451768
Posted Saturday, May 11, 2013 10:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
You can use a LEFT JOIN, or MERGE. Either should be able to insert only the rows that are missing.

This is rather short since you did not provide DDL and some sample data that you need.

Read about both in the SQL BOL.

Andrew SQLDBA
Post #1451835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse