SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fastest way to move data between two databases


Fastest way to move data between two databases

Author
Message
robert.wiglesworth
robert.wiglesworth
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 204
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!
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4762 Visits: 3427
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search