Batch Archiving with OUTPUT

• Xedni

  SSCertifiable

  Points: 6951

  More actions

  October 13, 2015 at 4:51 pm

  #312208

  Hey all,

  I have to set up archiving of some pretty large tables (125M+ rows) and I'm trying to figure out the best way to do it.

  I've been using OUTPUT into, something like this:

  delete top (100000) a

  output deleted.RecordID

  into dbo.RecordsArchive (RecordID)

  from dbo.Records

  It's difficult to tell if it sub-optimal code or just sheer volume, but does anyone know if there's any performance penalty using OUTPUT into in this way? or is it advantageous to insert the records to the archive first, and then delete them in two separate operations?

  Executive Junior Cowboy Developer, Esq.[/url]