Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Friday, May 10, 2013 4:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 1:55 PM
Points: 62, Visits: 180
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

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 24, 2016 12:43 PM
Points: 1,004, Visits: 3,427
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.

Post #1451835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse