I have 2 databases: Current Database & Archive Database.

I need to import the archive data back into the main database. There are 174,425,460 records that need to be pushed back to the main database.

Currently, I'm running an INSERT INTO, SELECT WHERE NOT EXISTS query in 10,000 row chunks, moving about 100,000 records an hour. Inserts are based on the clustered index column. Indexes aren't fragmented so I figure the page splits are what's taking the majority of the time.

This is going to be a weekend only process as to not affect production customers. It's going to take months to move the data this way! Does anyone have recommendations on speeding this process up?

  • 1
    I suggest you just build a Distributed Partitioned View instead. – John Eisbrener Apr 14 '17 at 15:04
  • This question have some ideas about related topic. dba.stackexchange.com/questions/59193/… – SqlWorldWide Apr 14 '17 at 15:15
  • How many records are in the main database? Maybe it would make more sense to copy the archived records to a new table in the main database, insert the records from production into the new table as well, then swap the tables. If all the records could be inserted into the new table during a scheduled outage, you wouldn't even have to worry about updating the records. NOTE: If you're going to do this, you should remove the archived records you've already moved form one of the two tables.... – RDFozz Apr 14 '17 at 15:15
  • Need to see the execution plan. Post on pastetheplan.com (disclaimer: I work for the company that owns it). Are the databases on the same instance? Is there an identity column? – Tara Kizer Apr 14 '17 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.